/******************************************************************************
 * @File name   :      MaintainCarPlateDaoImpl.java
 *
 * @Author      :      ANMA
 *
 * @Date        :      Jul 21, 2015
 *
 * @Copyright Notice: 
 * Copyright (c) 2014 DMS, Inc. All  Rights Reserved.
 * This software is published under the terms of the DMS Software
 * License version 1.0, a copy of which has been included with this
 * distribution in the LICENSE.txt file.
 * 
 * ----------------------------------------------------------------------------
 * Date                         Who         Version         Comments
 * Jul 21, 2015 5:20:03 PM    ANMA      1.0            Initial Version
 *****************************************************************************/
package com.cap.bts.crmdp.dealer.logistic.dao.impl;

import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import javax.inject.Inject;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceException;
import javax.persistence.Query;

import org.apache.log4j.Logger;
import org.hibernate.engine.SessionImplementor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.MessageSource;
import org.springframework.orm.jpa.JpaCallback;
import org.springframework.stereotype.Repository;

import com.cap.bts.crmdp.dealer.logistic.constants.LogisticConstants;
import com.cap.bts.crmdp.dealer.logistic.dao.IMaintainLogisticDao;
import com.cap.bts.crmdp.dealer.logistic.domain.LogisticProcess;
import com.cap.bts.crmdp.dealer.logistic.dto.LogisticDataDto;
import com.cap.bts.crmdp.dealer.logistic.dto.LogisticImportResultDto;
import com.cap.bts.crmdp.dealer.logistic.dto.LogisticPrintDataDto;
import com.cap.bts.crmdp.dealer.logistic.dto.LogisticQueryDto;
import com.cap.bts.crmdp.dealer.logistic.dto.LogisticRelatedDto;
import com.cap.bts.crmdp.dealer.report.dto.InventoryDataDto;
import com.cap.bts.crmdp.dealer.report.dto.MemberChartDto;
import com.cap.bts.crmdp.dealer.report.dto.MemberReportDetailsDto;
import com.cap.bts.crmdp.dealer.report.dto.MemberReportDto;
import com.cap.bts.crmdp.dealer.report.dto.ReportQueryDto;
import com.cap.bts.crmdp.dealer.report.dto.SalesReportDetailsDto;
import com.cap.bts.crmdp.dealer.report.dto.SalesReportDto;
import com.cap.bts.framework.common.constant.CommonConstants;
import com.cap.bts.framework.common.dao.callback.NativeQueryBySqlJpaCallback;
import com.cap.bts.framework.common.dao.callback.NativeQueryWithPagingJpaCallback;
import com.cap.bts.framework.common.dao.impl.BaseDaoImpl;
import com.cap.bts.framework.common.exception.AppException;
import com.cap.bts.framework.common.util.Pager;
import com.cap.bts.framework.common.util.Utils;

/**
 * 
 */
@Repository("maintainLogisticDao")
public class MaintainLogisticDaoImpl extends BaseDaoImpl<LogisticProcess, Long> implements
    IMaintainLogisticDao {

    @Autowired
    private MessageSource messageSource;
    private static final Logger LOGGER = Logger.getLogger(MaintainLogisticDaoImpl.class);

    @Inject
    public MaintainLogisticDaoImpl(
        @Value("com.cap.bts.crmdp.dealer.logistic.domain.LogisticProcess") Class<LogisticProcess> clazz) {
        super(clazz);
    }

    @Override
    public String preRequest(LogisticQueryDto queryDto) {
        String preRequests = "";
        if (Utils.notEmpty(queryDto.getTransactionStartDate())) {
            preRequests += " and TO_DAYS(a.ship_date) >= TO_DAYS('"
                + queryDto.getTransactionStartDate() + "')";
        }
        if (Utils.notEmpty(queryDto.getTransactionEndDate())) {
            preRequests += " and TO_DAYS(a.ship_date) <= TO_DAYS('"
                + queryDto.getTransactionEndDate() + "')";
        }
        if (Utils.notEmpty(queryDto.getDealerInfo())) {
            preRequests += " and (a.DEALER_NUM like '%" + queryDto.getDealerInfo() + "%'"
                + " or a.DEALER_NAME like '%" + queryDto.getDealerInfo() + "%')";
        }
        if (Utils.notEmpty(queryDto.getOrderNum())) {
            preRequests += " and a.po_num like '%" + queryDto.getOrderNum() + "%'";
        }
        if (Utils.notEmpty(queryDto.getExpressCom())) {
            preRequests += " and a.logistics_provider like '%" + queryDto.getExpressCom() + "%'";
        }
        if (Utils.notEmpty(queryDto.getExpressNum())) {
            preRequests += " and a.logistics_tracking_num like '%" + queryDto.getExpressNum()
                + "%'";
        }
        if (Utils.notEmpty(queryDto.getOrderStatus())) {
            preRequests += " and a.status ='" + queryDto.getOrderStatus() + "'";
        }
        if("".equals(preRequests)){
            return preRequests;
        }else{
            return " where"+preRequests.substring(4);
        }
    }

    @Override
    public List<LogisticDataDto> queryLogisticData(LogisticQueryDto queryDto) {
        List<LogisticDataDto> result = new ArrayList<LogisticDataDto>();
        String sql = "select DEALER_NUM,DEALER_NAME,po_num,NAME,shipped_quantity,ship_date,"
            + "estimated_arrival_date,logistics_provider,logistics_tracking_num,receive_date,"
            + "received_quantity,status,id from vt_logistic_process a"
            + preRequest(queryDto) + " order by a.create_date desc";
        String count = "select count(*) from vt_logistic_process a"
            + preRequest(queryDto);

        Pager page = queryDto.getPager();
        page.setPageSize(10);

        List<Object[]> objectList = getJpaTemplate().execute(
            new NativeQueryWithPagingJpaCallback<Object[]>(sql, count, queryDto.getPager()));

        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            LogisticDataDto data = new LogisticDataDto();
            data.setDealerNo(Utils.getString(resultCols[0]));
            data.setDealerName(Utils.getString(resultCols[1]));
            data.setOrderNo(Utils.getString(resultCols[2]));
            data.setCardName(Utils.getString(resultCols[3]));
            data.setShipAmount(Utils.getString(resultCols[4]));
            if (!"".equals(Utils.getString(resultCols[5]))) {
                data.setShipDate(new SimpleDateFormat(CommonConstants.DATE_FORMAT_YYMMDD)
                    .format(Utils.getDate(resultCols[5])));
            }
            if (!"".equals(Utils.getString(resultCols[6]))) {
                data.setEstimatedArriveDate(new SimpleDateFormat(CommonConstants.DATE_FORMAT_YYMMDD)
                    .format(Utils.getDate(resultCols[6])));
            }
            data.setExpressCom(Utils.getString(resultCols[7]));
            data.setExpressNo(Utils.getString(resultCols[8]));
            if (!"".equals(Utils.getString(resultCols[9]))) {
                data.setReceiveDate(new SimpleDateFormat(CommonConstants.DATE_FORMAT_YYMMDD)
                    .format(Utils.getDate(resultCols[9])));
            }
            data.setReceiveAmount(Utils.getString(resultCols[10]));
            if ("0".equals(Utils.getString(resultCols[11]))) {
                data.setStatus(messageSource.getMessage(LogisticConstants.STATUS_SENDING, null,
                    Locale.CHINA));
            } else if ("1".equals(Utils.getString(resultCols[11]))) {
                data.setStatus(messageSource.getMessage(LogisticConstants.STATUS_RECEIVED, null,
                    Locale.CHINA));
            }
            data.setId(Long.parseLong(Utils.getString(resultCols[12])));
            result.add(data);
        }
        return result;
    }

    /*
     * extract out all records qualified
     * @see com.cap.bts.crmdp.dealer.logistic.dao.IMaintainLogisticDao#logisticDataForPrintGroup(com.cap.bts.crmdp.dealer.logistic.dto.LogisticQueryDto)
     */
    @Override
    public List<LogisticDataDto> queryAllLogisticData(LogisticQueryDto queryDto) {
        List<LogisticDataDto> result = new ArrayList<LogisticDataDto>();
        String sql = "select DEALER_NUM,DEALER_NAME,po_num,NAME,shipped_quantity,ship_date,"
            + "estimated_arrival_date,logistics_provider,logistics_tracking_num,receive_date,"
            + "received_quantity,status,id from vt_logistic_process a"
            + preRequest(queryDto) + " order by a.create_date desc";

        List<Object[]> objectList = getJpaTemplate().execute(
                new NativeQueryWithPagingJpaCallback<Object[]>(sql, "select 1 from vt_logistic_process a limit 1", null));

        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            LogisticDataDto data = new LogisticDataDto();
            data.setDealerNo(Utils.getString(resultCols[0]));
            data.setDealerName(Utils.getString(resultCols[1]));
            data.setOrderNo(Utils.getString(resultCols[2]));
            data.setCardName(Utils.getString(resultCols[3]));
            data.setShipAmount(Utils.getString(resultCols[4]));
            if (!"".equals(Utils.getString(resultCols[5]))) {
                data.setShipDate(new SimpleDateFormat(CommonConstants.DATE_FORMAT_YYMMDD)
                    .format(Utils.getDate(resultCols[5])));
            }
            if (!"".equals(Utils.getString(resultCols[6]))) {
                data.setEstimatedArriveDate(new SimpleDateFormat(CommonConstants.DATE_FORMAT_YYMMDD)
                    .format(Utils.getDate(resultCols[6])));
            }
            data.setExpressCom(Utils.getString(resultCols[7]));
            data.setExpressNo(Utils.getString(resultCols[8]));
            if (!"".equals(Utils.getString(resultCols[9]))) {
                data.setReceiveDate(new SimpleDateFormat(CommonConstants.DATE_FORMAT_YYMMDD)
                    .format(Utils.getDate(resultCols[9])));
            }
            data.setReceiveAmount(Utils.getString(resultCols[10]));
            if ("0".equals(Utils.getString(resultCols[11]))) {
                data.setStatus(messageSource.getMessage(LogisticConstants.STATUS_SENDING, null,
                    Locale.CHINA));
            } else if ("1".equals(Utils.getString(resultCols[11]))) {
                data.setStatus(messageSource.getMessage(LogisticConstants.STATUS_RECEIVED, null,
                    Locale.CHINA));
            }
            data.setId(Long.parseLong(Utils.getString(resultCols[12])));
            result.add(data);
        }
        return result;
    }

    @Override
    public List<LogisticQueryDto> logisticDataForPrintGroup(LogisticQueryDto queryDto) {
        List<LogisticQueryDto> result = new ArrayList<LogisticQueryDto>();
        String sql = "select dealer_id,ship_date,transaction_no " + "from tt_logistic_process a "
            + preRequest(queryDto)
            + " group by dealer_id,ship_date,transaction_no";

        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));

        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            LogisticQueryDto data = new LogisticQueryDto();
            data.setDealerNo(Utils.getString(resultCols[0]));
            data.setShipDate(Utils.getString(resultCols[1]));
            data.setTransactionNo(Utils.getString(resultCols[2]));
            result.add(data);
        }
        return result;
    }

    @Override
    public List<LogisticPrintDataDto> queryLogisticDataForPrint(LogisticQueryDto queryDto,
        List<LogisticQueryDto> dto) {
        List<LogisticPrintDataDto> result = new ArrayList<LogisticPrintDataDto>();
        for (int i = 0; i < dto.size(); i++) {
            List<LogisticDataDto> cards = new ArrayList<LogisticDataDto>();
            String sql = "select DEALER_NUM,DEALER_NAME,po_num,NAME,shipped_quantity,ship_date,"
                + "estimated_arrival_date,logistics_provider,logistics_tracking_num,receive_date,"
                + "received_quantity,status,id from vt_logistic_process a"
                + preRequest(queryDto)
                + " and a.dealer_id=" + dto.get(i).getDealerNo()
                + " and TO_DAYS(a.ship_date) = TO_DAYS('" + dto.get(i).getShipDate() + "')";
            if (Utils.isNullOrEmpty(dto.get(i).getTransactionNo())) {
                sql = sql + " and a.transaction_no is null";
            } else {
                sql = sql + " and a.transaction_no='" + dto.get(i).getTransactionNo() + "'";
            }
            sql = sql + " order by a.DEALER_NUM,a.ship_date desc,a.id,a.transaction_no";
            List<Object[]> objectList = getJpaTemplate().executeFind(
                new NativeQueryBySqlJpaCallback<Object[]>(sql));
            if (objectList != null && !objectList.isEmpty()) {

                LogisticPrintDataDto data = new LogisticPrintDataDto();
                for (int j = 0; j < objectList.size(); j++) {
                    Object[] resultCols = objectList.get(j);
                    data.setId(String.valueOf(i));
                    data.setDealerNo(Utils.getString(resultCols[0]));
                    data.setDealerName(Utils.getString(resultCols[1]));
                    data.setOrderNo(Utils.getString(resultCols[2]));
                    data.setExpressCom(Utils.getString(resultCols[7]));
                    data.setExpressNo(Utils.getString(resultCols[8]));
                    LogisticDataDto card = new LogisticDataDto();
                    card.setCardName(Utils.getString(resultCols[3]));
                    card.setShipAmount(Utils.getString(resultCols[4]));
                    if (!"".equals(Utils.getString(resultCols[5]))) {
                        card.setShipDate(new SimpleDateFormat(CommonConstants.DATE_FORMAT_YYMMDD)
                            .format(Utils.getDate(resultCols[5])));
                    }
                    if (!"".equals(Utils.getString(resultCols[6]))) {
                        card.setEstimatedArriveDate(new SimpleDateFormat(
                            CommonConstants.DATE_FORMAT_YYMMDD).format(Utils.getDate(resultCols[6])));
                    }
                    if (!"".equals(Utils.getString(resultCols[9]))) {
                        card.setReceiveDate(new SimpleDateFormat(CommonConstants.DATE_FORMAT_YYMMDD)
                            .format(Utils.getDate(resultCols[9])));
                    }
                    card.setReceiveAmount(Utils.getString(resultCols[10]));
                    if ("0".equals(Utils.getString(resultCols[11]))) {
                        card.setStatus(messageSource.getMessage(LogisticConstants.STATUS_SENDING,
                            null, Locale.CHINA));
                    } else if ("1".equals(Utils.getString(resultCols[11]))) {
                        card.setStatus(messageSource.getMessage(LogisticConstants.STATUS_RECEIVED,
                            null, Locale.CHINA));
                    }
                    card.setId(Long.parseLong(Utils.getString(resultCols[12])));
                    cards.add(card);
                }
                data.setCardList(cards);
                result.add(data);
            }
        }
        return result;
    }

    @Override
    public List<String> getDealerName(String dealerNo) {
        List<String> result = new ArrayList<String>();
        String sql = "select dealer_num,dealer_name from tm_raw_dealer "
            + "where dealer_num like '" + dealerNo + "%'";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            String data = "{\"" + Utils.getString(resultCols[0]) + "\":\" "
                + Utils.getString(resultCols[1]) + "\"}";
            result.add(data);
        }
        return result;
    }

    @Override
    public Map<String, String> getDealers() {
        Map<String, String> result = new HashMap<String, String>();
        String sql = "select dealer_num,dealer_name from tm_raw_dealer";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            result.put(Utils.getString(resultCols[0]), Utils.getString(resultCols[1]));
        }
        return result;
    }

    @Override
    public List<LogisticRelatedDto> getCardName() {
        List<LogisticRelatedDto> result = new ArrayList<LogisticRelatedDto>();
        String sql = "select id,name from tm_crm_card_definition where status=1";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            LogisticRelatedDto data = new LogisticRelatedDto();
            data.setId(Long.parseLong(Utils.getString(resultCols[0])));
            data.setCardName(Utils.getString(resultCols[1]));
            data.setType("0");
            result.add(data);
        }
        return result;
    }
    
    @Override
    public List<LogisticRelatedDto> getSMSName(){
        List<LogisticRelatedDto> result = new ArrayList<LogisticRelatedDto>();
        String sql = "select id,code_value from tc_code_value where code_type='SMS'";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            LogisticRelatedDto data = new LogisticRelatedDto();
            data.setId(Long.parseLong(Utils.getString(resultCols[0])));
            data.setCardName(Utils.getString(resultCols[1]));
            data.setType("1");
            result.add(data);
        }
        return result;
    }

    @Override
    public List<LogisticRelatedDto> getPackageName() {
        List<LogisticRelatedDto> result = new ArrayList<LogisticRelatedDto>();
        String sql = "select id,name from tm_crm_package_definition where status=1";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            LogisticRelatedDto data = new LogisticRelatedDto();
            data.setId(Long.parseLong(Utils.getString(resultCols[0])));
            data.setPackageName(Utils.getString(resultCols[1]));
            result.add(data);
        }
        return result;
    }
    
    @Override
    public List<String> existCardName() {
        List<String> result = new ArrayList<String>();
        String sql = "select id,name from tm_crm_card_definition where status=1";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            result.add(Utils.getString(resultCols[1]));
        }
        return result;
    }

    @Override
    public String getDealerId(String dealerNo) {
        String result = "";
        String sql = "select id,dealer_num from tm_raw_dealer " + "where dealer_num ='" + dealerNo
            + "'";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            result = Utils.getString(resultCols[0]);
        }
        return result;
    }

    @Override
    public String getCardId(String cardName) {
        String result = "";
        String sql = "select id,name from tm_crm_card_definition where status=1 "
            + "and name like '%" + cardName + "%'";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            result = Utils.getString(resultCols[0]);
        }
        return result;
    }

    @Override
    public void insertLogisticInfo(LogisticDataDto newDto, String dealerId, String cardDefId) {
        PreparedStatement stm = null;
        try {
            SessionImplementor session = getEntityManager().unwrap(SessionImplementor.class);
            Connection conn = session.connection();
            stm = conn
                .prepareStatement("insert into tt_logistic_process(dealer_id,po_num,card_def_id,shipped_quantity,"
                    + "ship_date,estimated_arrival_date,logistics_provider,logistics_tracking_num,create_by,create_date,"
                    + "last_update_by,last_update_date,status,transaction_no,type) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

            stm.setString(1, dealerId);
            stm.setString(2, newDto.getOrderNo());
            stm.setString(3, cardDefId);
            stm.setString(4, newDto.getShipAmount());
            stm.setDate(5, newDto.getShipDateVal());
            stm.setDate(6, newDto.getEstimatedArriveDateVal());
            stm.setString(7, newDto.getExpressCom());
            stm.setString(8, newDto.getExpressNo());
            stm.setString(9, newDto.getUserId());
            stm.setTimestamp(10, new Timestamp(new Date().getTime()));
            stm.setString(11, newDto.getUserId());
            stm.setTimestamp(12, new Timestamp(new Date().getTime()));
            stm.setString(13, "0");
            stm.setString(14, newDto.getTransactionNo());
            if(newDto.getType() == null){
                newDto.setType("0");
            }
            stm.setString(15, newDto.getType());
            stm.executeUpdate();
        } catch (Exception ae) {
            LOGGER.error("insertLogisticInfo ", ae);
        } finally {
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException e) {
                    LOGGER.error("insertLogisticInfo ", e);
                }
            }
        }
    }

    @Override
    public LogisticDataDto queryLogisticDataById(String id) {
        LogisticDataDto result = new LogisticDataDto();
        String sql = "select a.DEALER_NUM,a.DEALER_NAME,a.po_num,a.NAME,a.shipped_quantity,"
            + "a.ship_date,a.estimated_arrival_date,a.logistics_provider,"
            + "a.logistics_tracking_num,a.receive_date,a.received_quantity,a.status,a.id "
            + "from vt_logistic_process a "
            + "where a.id='" + id + "'";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultColsById = objectList.get(i);
            LogisticDataDto dataById = new LogisticDataDto();
            dataById.setDealerNo(Utils.getString(resultColsById[0]));
            dataById.setDealerName(Utils.getString(resultColsById[1]));
            dataById.setOrderNo(Utils.getString(resultColsById[2]));
            dataById.setCardName(Utils.getString(resultColsById[3]));
            dataById.setShipAmount(Utils.getString(resultColsById[4]));
            if (!"".equals(Utils.getString(resultColsById[5]))) {
                dataById.setShipDate(new SimpleDateFormat(CommonConstants.DATE_FORMAT_YYMMDD)
                    .format(Utils.getDate(resultColsById[5])));
            }
            if (!"".equals(Utils.getString(resultColsById[6]))) {
                dataById.setEstimatedArriveDate(new SimpleDateFormat(
                    CommonConstants.DATE_FORMAT_YYMMDD).format(Utils.getDate(resultColsById[6])));
            }
            dataById.setExpressCom(Utils.getString(resultColsById[7]));
            dataById.setExpressNo(Utils.getString(resultColsById[8]));
            if (!"".equals(Utils.getString(resultColsById[9]))) {
                dataById.setReceiveDate(new SimpleDateFormat(CommonConstants.DATE_FORMAT_YYMMDD)
                    .format(Utils.getDate(resultColsById[9])));
            }
            dataById.setReceiveAmount(Utils.getString(resultColsById[10]));
            if ("0".equals(Utils.getString(resultColsById[11]))) {
                dataById.setStatus(messageSource.getMessage(LogisticConstants.STATUS_SENDING, null,
                    Locale.CHINA));
            } else if ("1".equals(Utils.getString(resultColsById[11]))) {
                dataById.setStatus(messageSource.getMessage(LogisticConstants.STATUS_RECEIVED,
                    null, Locale.CHINA));
            }
            dataById.setId(Long.parseLong(Utils.getString(resultColsById[12])));
            result = dataById;
        }
        return result;
    }

    @Override
    public Integer updateLogisticInfo(final String id, final int shipAmount, final Date shipDate,
        final Date estimatedArriveDate, final String expressCom, final String expressNo,
        final String editor) {
        final String sql = "update tt_logistic_process "
            + "set SHIPPED_QUANTITY=?2,SHIP_DATE=?3,ESTIMATED_ARRIVAL_DATE=?4,"
            + "LOGISTICS_PROVIDER=?5,LOGISTICS_TRACKING_NUM=?6,last_update_date=?7,"
            + "last_update_by=?8 where id=?1";
        return getJpaTemplate().execute(new JpaCallback<Integer>() {
            @Override
            public Integer doInJpa(EntityManager em) throws PersistenceException {
                Query query = em.createNativeQuery(sql);
                query.setParameter(1, id);
                query.setParameter(2, shipAmount);
                query.setParameter(3, shipDate);
                query.setParameter(4, estimatedArriveDate);
                query.setParameter(5, expressCom);
                query.setParameter(6, expressNo);
                query.setParameter(7, new Date());
                query.setParameter(8, editor);
                return query.executeUpdate();
            }
        });
    }

    @Override
    public String getAmountById(String id) {
        String result = "";
        String sql = "select id,SHIPPED_QUANTITY from tt_logistic_process " + "where id ='" + id + "'";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            result = Utils.getString(resultCols[1]);
        }
        return result;
    }

    @Override
    public Integer deleteLogisticInfo(final String id) {
        final String sql = "delete from tt_logistic_process where id=?1";
        return getJpaTemplate().execute(new JpaCallback<Integer>() {
            @Override
            public Integer doInJpa(EntityManager em) throws PersistenceException {
                Query query = em.createNativeQuery(sql);
                query.setParameter(1, id);
                return query.executeUpdate();
            }
        });
    }

    @Override
    public List<LogisticDataDto> queryReceiveLogisticData(LogisticQueryDto queryDto) {
        List<LogisticDataDto> result = new ArrayList<LogisticDataDto>();
        String sql = "select a.po_num,a.NAME,a.shipped_quantity,"
            + "a.ship_date,a.logistics_provider,a.logistics_tracking_num,"
            + "a.receive_date,a.received_quantity,a.status,a.id,a.card_def_id,a.estimated_arrival_date "
            + "from vt_logistic_process a "
            + "where a.type=0 and a.dealer_num='"
            + queryDto.getDealerInfo() + "'";
        String count = "select count(*) from vt_logistic_process a "
            + "where a.type=0 and a.dealer_num='"
            + queryDto.getDealerInfo() + "'";
        if(!"".endsWith(preRequest(queryDto))){
            sql = sql + " and" + preRequest(queryDto).substring(6);
            count = count + " and" + preRequest(queryDto).substring(6);
        }
            sql = sql + " order by a.status asc,a.receive_date desc";

        Pager page = queryDto.getPager();
        page.setPageSize(10);

        List<Object[]> objectList = getJpaTemplate().execute(
            new NativeQueryWithPagingJpaCallback<Object[]>(sql, count, queryDto.getPager()));

        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            LogisticDataDto receiveData = new LogisticDataDto();
            receiveData.setOrderNo(Utils.getString(resultCols[0]));
            receiveData.setCardName(Utils.getString(resultCols[1]));
            receiveData.setShipAmount(Utils.getString(resultCols[2]));
            if (!"".equals(Utils.getString(resultCols[3]))) {
                receiveData.setShipDate(new SimpleDateFormat(CommonConstants.DATE_FORMAT_YYMMDD)
                    .format(Utils.getDate(resultCols[3])));
            }
            receiveData.setExpressCom(Utils.getString(resultCols[4]));
            receiveData.setExpressNo(Utils.getString(resultCols[5]));
            if (!"".equals(Utils.getString(resultCols[6]))) {
                receiveData.setReceiveDate(new SimpleDateFormat(CommonConstants.DATE_FORMAT_YYMMDD)
                    .format(Utils.getDate(resultCols[6])));
            }
            if ("0".equals(Utils.getString(resultCols[8]))) {
                receiveData.setReceiveAmount(Utils.getString(resultCols[2]));
                receiveData.setStatus(messageSource.getMessage(LogisticConstants.STATUS_SENDING,
                    null, Locale.CHINA));
            } else if ("1".equals(Utils.getString(resultCols[8]))) {
                receiveData.setReceiveAmount(Utils.getString(resultCols[7]));
                receiveData.setStatus(messageSource.getMessage(LogisticConstants.STATUS_RECEIVED,
                    null, Locale.CHINA));
            }
            receiveData.setId(Long.parseLong(Utils.getString(resultCols[9])));
            receiveData.setCardDefId(Utils.getString(resultCols[10]));
            if (!"".equals(Utils.getString(resultCols[11]))) {
                receiveData.setEstimatedArriveDate(new SimpleDateFormat(
                    CommonConstants.DATE_FORMAT_YYMMDD).format(Utils.getDate(resultCols[11])));
            }
            result.add(receiveData);
        }
        return result;
    }

    @Override
    public Integer receiveLogistic(final String id, final int receiveAmount, final String editor) {
        final String sql = "update tt_logistic_process "
            + "set received_quantity=?2,last_update_date=?3,last_update_by=?4,receive_date=?5,status=1 "
            + "where id=?1";
        return getJpaTemplate().execute(new JpaCallback<Integer>() {
            @Override
            public Integer doInJpa(EntityManager em) throws PersistenceException {
                Query query = em.createNativeQuery(sql);
                query.setParameter(1, id);
                query.setParameter(2, receiveAmount);
                query.setParameter(3, new Date());
                query.setParameter(4, editor);
                query.setParameter(5, new Date());
                return query.executeUpdate();
            }
        });
    }

    @Override
    public String queryCardInventoryInfo(String dealerId, String cardDefId) {
        String result = "";
        String sql = "select id,onhand_quantity from tm_card_inventory where dealer_id='"
            + dealerId + "'" + " and card_def_id='" + cardDefId + "'";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            result = Utils.getString(resultCols[0]) + "," + Utils.getString(resultCols[1]);
        }
        return result;
    }

    @Override
    public Integer updateCardInventory(final String id, final int amount, final String editor) {
        final String sql = "update tm_card_inventory "
            + "set onhand_quantity=?2,last_update_date=?3,last_update_by=?4 where id=?1";
        return getJpaTemplate().execute(new JpaCallback<Integer>() {
            @Override
            public Integer doInJpa(EntityManager em) throws PersistenceException {
                Query query = em.createNativeQuery(sql);
                query.setParameter(1, id);
                query.setParameter(2, amount);
                query.setParameter(3, new Date());
                query.setParameter(4, editor);
                return query.executeUpdate();
            }
        });
    }

    @Override
    public void insertCardInventory(final String dealerId, final String cardDefId,
        final int amount, final String editor) {
        PreparedStatement stm = null;
        try {
            SessionImplementor session = getEntityManager().unwrap(SessionImplementor.class);
            Connection conn = session.connection();
            stm = conn
                .prepareStatement("insert into tm_card_inventory(dealer_id,card_def_id,onhand_quantity,"
                    + "create_by,create_date,last_update_by,last_update_date) values(?,?,?,?,?,?,?)");

            stm.setString(1, dealerId);
            stm.setString(2, cardDefId);
            stm.setInt(3, amount);
            stm.setString(4, dealerId);
            stm.setTimestamp(5, new Timestamp(new Date().getTime()));
            stm.setString(6, dealerId);
            stm.setTimestamp(7, new Timestamp(new Date().getTime()));
            stm.executeUpdate();
        } catch (Exception ae) {
            LOGGER.error("insertCardInventory ", ae);
        } finally {
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException e) {
                    LOGGER.error("insertCardInventory ", e);
                }
            }
        }
    }

    @Override
    public void deleteAll() {
        this.getEntityManager().createNativeQuery("delete from tt_logistic_process_temp")
            .executeUpdate();
    }

    @Override
    public void saveLogisticToTemp(List<LogisticDataDto> dataList) throws AppException {
        PreparedStatement stm = null;
        try {
            SessionImplementor session = getEntityManager().unwrap(SessionImplementor.class);
            Connection conn = session.connection();
            stm = conn
                .prepareStatement(" insert into tt_logistic_process_temp(dealer_no,card_name,ship_date,estimated_arrive_date,amount,line_no) "
                    + " values(?,?,?,?,?,?)");
            for (int i = 1; i <= dataList.size(); i++) {
                LogisticDataDto dto = dataList.get(i - 1);
                stm.setString(1, dto.getDealerNo());
                stm.setString(2, dto.getCardName());
                stm.setString(3, dto.getShipDate());
                stm.setString(4, dto.getEstimatedArriveDate());
                stm.setString(5, dto.getShipAmount());
                stm.setString(6, String.valueOf(i + 1));
                int linkCount = stm.executeUpdate();
                LOGGER.info("execute succeed " + linkCount);
            }
        } catch (Exception e) {
            throw new AppException("insert_into_tt_logistic_process_temp_exception", e);
        } finally {
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException se) {
                    LOGGER.error("saveLogisticToTemp ", se);
                }
            }
        }
    }

    @Override
    public List<LogisticImportResultDto> getWrongDealerNo() {
        List<LogisticImportResultDto> result = new ArrayList<LogisticImportResultDto>();
        String sql = "select a.dealer_no,a.line_no,a.card_name,a.ship_date,a.estimated_arrive_date "
            + "from  tt_logistic_process_temp a "
            + "where not exists"
            + "(select 1 from tm_raw_dealer b where b.dealer_num=a.dealer_no)";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            LogisticImportResultDto data = new LogisticImportResultDto();
            data.setDealerNo(Utils.getString(resultCols[0]));
            data.setLineNo(Utils.getString(resultCols[1]));
            data.setCardName(Utils.getString(resultCols[2]));
            data.setShipDate(Utils.getString(resultCols[3]));
            data.setEstimateDate(Utils.getString(resultCols[4]));
            data.setReason(messageSource.getMessage(LogisticConstants.WRONG_DEALER, null,
                Locale.CHINA));
            result.add(data);
        }
        return result;
    }

    @Override
    public List<LogisticImportResultDto> getWrongCardName() {
        List<LogisticImportResultDto> result = new ArrayList<LogisticImportResultDto>();
        String sql = "select a.card_name,a.line_no,a.dealer_no,a.ship_date,a.estimated_arrive_date "
            + "from  tt_logistic_process_temp a where not exists"
            + "(select 1 from tm_crm_card_definition b where b.name=a.card_name)";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            LogisticImportResultDto data = new LogisticImportResultDto();
            data.setCardName(Utils.getString(resultCols[0]));
            data.setLineNo(Utils.getString(resultCols[1]));
            data.setDealerNo(Utils.getString(resultCols[2]));
            data.setShipDate(Utils.getString(resultCols[3]));
            data.setEstimateDate(Utils.getString(resultCols[4]));
            data.setReason(messageSource.getMessage(LogisticConstants.WRONG_CARD, null,
                Locale.CHINA));
            result.add(data);
        }
        return result;
    }

    @Override
    public List<LogisticImportResultDto> getWrongEstimate() {
        List<LogisticImportResultDto> result = new ArrayList<LogisticImportResultDto>();
        String sql = "select ship_date,estimated_arrive_date,line_no,card_name,dealer_no "
            + "from tt_logistic_process_temp where"
            + " TO_DAYS(ship_date) > TO_DAYS(estimated_arrive_date)";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            LogisticImportResultDto data = new LogisticImportResultDto();
            data.setShipDate(Utils.getString(resultCols[0]));
            data.setEstimateDate(Utils.getString(resultCols[1]));
            data.setLineNo(Utils.getString(resultCols[2]));
            data.setCardName(Utils.getString(resultCols[3]));
            data.setDealerNo(Utils.getString(resultCols[4]));
            data.setReason(messageSource.getMessage(LogisticConstants.WRONG_ESTIMATE, null,
                Locale.CHINA));
            result.add(data);
        }
        return result;
    }

    @Override
    public String getTransactionEndDay(String endMonth) {
        int year = Integer.parseInt(endMonth.substring(0, 4));
        int month = Integer.parseInt(endMonth.substring(5));
        String day = "-28";
        List<Integer> thrtyfrst = Arrays.asList(1, 3, 5, 7, 8, 10, 12);
        List<Integer> thrty = Arrays.asList(4, 6, 9, 11);
        if (year % 4 == 0) {
            if (year % 100 == 0) {
                if (year % 400 == 0) { // 能被400整除的,是闰年
                    day = "-29";
                }
            } else { // 能被4整除,但不能被100整除的,是闰年
                day = "-29";
            }
        }
        if (thrtyfrst.contains(month)) {
            return endMonth + "-31";
        } else if (thrty.contains(month)) {
            return endMonth + "-30";
        } else {
            return endMonth + day;
        }
    }

    @Override
    public List<InventoryDataDto> getMonthAmt(ReportQueryDto dto, List<InventoryDataDto> dataList) {
        for (int i = 0; i < dataList.size(); i++) {
            String sql = "select card_def_id,dealer_id,min(PURCHASE_DATE) from tm_member_package_info"
                + " where dealer_id in('" + dto.getUserId() + "')"
                + " and card_def_id='" + dataList.get(i).getCardId() + "'" 
                    + " and is_gift=0"
                + " group by card_def_id,dealer_id";
            List<Object[]> objectList = getJpaTemplate().executeFind(
                new NativeQueryBySqlJpaCallback<Object[]>(sql));
            if (objectList == null || objectList.isEmpty()) {
                dataList.get(i).setMonths(1);
            } else {
                for (int j = 0; j < objectList.size(); j++) {
                    Object[] resultCols = objectList.get(j);
                    int months = 1;
                    SimpleDateFormat sdy = new SimpleDateFormat("yyyy");
                    SimpleDateFormat sdm = new SimpleDateFormat("MM");
                    int startYear = Integer
                        .parseInt(Utils.getString(resultCols[2]).substring(0, 4));
                    int startMonth = Integer.parseInt(Utils.getString(resultCols[2])
                        .substring(5, 7));
                    int endYear = Integer.parseInt(sdy.format(new Date()));
                    int endMonth = Integer.parseInt(sdm.format(new Date()));
                    months = (endYear - startYear) * 12 + endMonth - startMonth + 1;
                    dataList.get(i).setMonths(months);
                }
            }
        }
        return dataList;
    }

    @Override
    public List<String> getDealerList(String dealerId){
        List<String> dealerIds = new ArrayList<String>();
        dealerIds.add(dealerId);
        String sql="select mars_tenant_id,dealer_id from tm_dealer_rel_mars where mars_tenant_id ="
                + "(select mars_tenant_id from tm_dealer_rel_mars where dealer_id='"+dealerId+"')";
        List<Object[]> objectList = getJpaTemplate().executeFind(
                new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return dealerIds;
            } else {
                for (int j = 0; j < objectList.size(); j++) {
                    Object[] resultCols = objectList.get(j);
                    if(!dealerId.equals(Utils.getString(resultCols[1]))){
                        dealerIds.add(Utils.getString(resultCols[1]));
                        }
                    }
                }
        return dealerIds;
        }
    
    @Override
    public String getDealers(String dealerId){
        StringBuilder dealerIds = new StringBuilder();
        String sql="select mars_tenant_id,dealer_id from tm_dealer_rel_mars where mars_tenant_id ="
                + "(select mars_tenant_id from tm_dealer_rel_mars where dealer_id='"+dealerId+"')";
        List<Object[]> objectList = getJpaTemplate().executeFind(
                new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return dealerId;
            } else {
                for (int j = 0; j < objectList.size(); j++) {
                    Object[] resultCols = objectList.get(j);
                    dealerIds.append(Utils.getString(resultCols[1])).append("','");
                    }
                }
        return dealerIds.toString();
        }
    
    @Override
    public List<InventoryDataDto> queryInventoryDataOrdered(ReportQueryDto dto,
        List<LogisticRelatedDto> dataList) {
        List<InventoryDataDto> result = new ArrayList<InventoryDataDto>();
        for (int i = 0; i < dataList.size(); i++) {
            String sql = "select card_def_id,sum(shipped_quantity) "
                + "from tt_logistic_process where" 
                /*+ " TO_DAYS(ship_date) >= TO_DAYS('"
                + dto.getTransactionStartDate() + "')" + " and TO_DAYS(ship_date) <= TO_DAYS('"
                + dto.getTransactionEndDate() + "') and"*/ 
                + " dealer_id in ('"+ dto.getHierarchy() + "')"
                + " and card_def_id='" + dataList.get(i).getId() + "'"
                + " group by card_def_id";
            List<Object[]> objectList = getJpaTemplate().executeFind(
                new NativeQueryBySqlJpaCallback<Object[]>(sql));
            if (objectList == null || objectList.isEmpty()) {
                InventoryDataDto data = new InventoryDataDto();
                data.setCardId(dataList.get(i).getId().toString());
                data.setCardName(dataList.get(i).getCardName());
                data.setOrderAmount("0");
                data.setDealerId(dto.getUserId());
                data.setHierarchy(dto.getHierarchy());
                result.add(data);
            } else {
                for (int j = 0; j < objectList.size(); j++) {
                    Object[] resultCols = objectList.get(j);
                    InventoryDataDto data = new InventoryDataDto();
                    data.setCardId(dataList.get(i).getId().toString());
                    data.setCardName(dataList.get(i).getCardName());
                    data.setOrderAmount(Utils.getString(resultCols[1]));
                    data.setDealerId(dto.getUserId());
                    data.setHierarchy(dto.getHierarchy());
                    result.add(data);
                }
            }
        }
        return result;
    }

    @Override
    public List<InventoryDataDto> queryInventoryDataSelled(ReportQueryDto dto,
        List<InventoryDataDto> dataList) {
        for (int i = 0; i < dataList.size(); i++) {
            String sql = "select card_def_id,count(id)" + " from tm_member_package_info"
                + " where PURCHASE_DATE is not null" 
                + " and TO_DAYS(PURCHASE_DATE) >= TO_DAYS('"
                + dto.getTransactionStartDate() + "')"
                + " and TO_DAYS(PURCHASE_DATE) <= TO_DAYS('"
                + dto.getTransactionEndDate() + "')" 
                + " and dealer_id in ('" + dto.getHierarchy() + "')" 
                + " and card_def_id='" + dataList.get(i).getCardId() + "'"
                + " and is_gift=0"
                + " group by card_def_id";
            List<Object[]> objectList = getJpaTemplate().executeFind(
                new NativeQueryBySqlJpaCallback<Object[]>(sql));
            if (objectList == null || objectList.isEmpty()) {
                dataList.get(i).setSellAmount("0");
            } else {
                for (int j = 0; j < objectList.size(); j++) {
                    Object[] resultCols = objectList.get(j);
                    dataList.get(i).setSellAmount(Utils.getString(resultCols[1]));
                }
            }
        }
        return dataList;
    }

    @Override
    public List<InventoryDataDto> queryInventoryAllSelled(ReportQueryDto dto,
        List<InventoryDataDto> dataList) {
        for (int i = 0; i < dataList.size(); i++) {
            String sql = "select card_def_id,count(id)" + " from tm_member_package_info"
                + " where PURCHASE_DATE is not null" 
                + " and dealer_id in ('" + dto.getHierarchy() + "')"
                + " and card_def_id='" + dataList.get(i).getCardId() + "'"
                + " and is_gift=0"
                + " group by card_def_id";
            List<Object[]> objectList = getJpaTemplate().executeFind(
                new NativeQueryBySqlJpaCallback<Object[]>(sql));
            if (objectList == null || objectList.isEmpty()) {
                dataList.get(i).setMonthlyAvg("0");
                dataList.get(i).setSuggestAmount("50");
            } else {
                for (int j = 0; j < objectList.size(); j++) {
                    Object[] resultCols = objectList.get(j);
                    int monAvg = Integer.parseInt(Utils.getString(resultCols[1]))
                        / dataList.get(i).getMonths();
                    BigDecimal a = new BigDecimal(monAvg);
                    monAvg = a.setScale(0, BigDecimal.ROUND_HALF_UP).intValue();
                    dataList.get(i).setMonthlyAvg(Integer.toString(monAvg));
                    double sgAmt = monAvg * 1.5;
                    BigDecimal b = new BigDecimal(sgAmt);
                    sgAmt = b.setScale(0, BigDecimal.ROUND_HALF_UP).intValue();
                    if (sgAmt < 50) {
                        dataList.get(i).setSuggestAmount("50");
                    } else {
                        dataList.get(i).setSuggestAmount(
                            Integer.toString(b.setScale(0, BigDecimal.ROUND_HALF_UP).intValue()));
                    }
                }
            }
        }
        return dataList;
    }

    @Override
    public List<InventoryDataDto> queryInventoryDataInventory(ReportQueryDto dto,
        List<InventoryDataDto> dataList) {
        for (int i = 0; i < dataList.size(); i++) {
            String sql = "select card_def_id,sum(onhand_quantity)"
                + " from tm_card_inventory" 
                + " where dealer_id in ('" + dto.getHierarchy() + "')"
                + " and card_def_id='" + dataList.get(i).getCardId() + "'"
                + " group by card_def_id";
            List<Object[]> objectList = getJpaTemplate().executeFind(
                new NativeQueryBySqlJpaCallback<Object[]>(sql));
            if (objectList == null || objectList.isEmpty()) {
                dataList.get(i).setInventoryAmount("0");
            } else {
                for (int j = 0; j < objectList.size(); j++) {
                    Object[] resultCols = objectList.get(j);
                    dataList.get(i).setInventoryAmount(Utils.getString(resultCols[1]));
                }
            }
        }
        return dataList;
    }

    @Override
    public List<InventoryDataDto> queryInventoryDataOnRoad(ReportQueryDto dto,
        List<InventoryDataDto> dataList) {
        for (int i = 0; i < dataList.size(); i++) {
            String sql = "select card_def_id,sum(shipped_quantity)"
                + " from tt_logistic_process" + " where status=0"
                + " and TO_DAYS(ship_date) >= TO_DAYS('" + dto.getTransactionStartDate() + "')"
                + " and TO_DAYS(ship_date) <= TO_DAYS('" + dto.getTransactionEndDate() + "')" 
                + " and dealer_id in ('" + dto.getHierarchy() + "')"
                + " and card_def_id='" + dataList.get(i).getCardId() + "'"
                + " group by card_def_id";
            List<Object[]> objectList = getJpaTemplate().executeFind(
                new NativeQueryBySqlJpaCallback<Object[]>(sql));
            if (objectList == null || objectList.isEmpty()) {
                dataList.get(i).setNotReceivedAmount("0");
            } else {
                for (int j = 0; j < objectList.size(); j++) {
                    Object[] resultCols = objectList.get(j);
                    dataList.get(i).setNotReceivedAmount(Utils.getString(resultCols[1]));
                }
            }
        }
        return dataList;
    }

    @Override
    public List<String> getAllMonth(ReportQueryDto dto) {
        List<String> result = new ArrayList<String>();
        int startYear = Integer.parseInt(dto.getTransactionStartDate().substring(0, 4));
        int endYear = Integer.parseInt(dto.getTransactionEndDate().substring(0, 4));
        int startMonth = Integer.parseInt(dto.getTransactionStartDate().substring(5,7));
        int endMonth = Integer.parseInt(dto.getTransactionEndDate().substring(5,7));
        if (startYear == endYear) {
            for (int j = startMonth; j <= endMonth; j++) {
                StringBuilder data = new StringBuilder();
                if (j < 10) {
                    data.append(startYear).append("-0").append(String.valueOf(j));
                } else {
                    data.append(startYear).append("-").append(String.valueOf(j));
                }
                result.add(data.toString());
            }
        } else {
            for (int i = startYear; i <= endYear; i++) {
                if (i == startYear) {
                    for (int j = startMonth; j <= 12; j++) {
                        StringBuilder data = new StringBuilder();
                        if (j < 10) {
                            data.append(String.valueOf(i)).append("-0").append(String.valueOf(j));
                        } else {
                            data.append(String.valueOf(i)).append("-").append(String.valueOf(j));
                        }
                        result.add(data.toString());
                    }
                } else if (i == endYear) {
                    for (int j = 1; j <= endMonth; j++) {
                        StringBuilder data = new StringBuilder();
                        if (j < 10) {
                            data.append(String.valueOf(i)).append("-0").append(String.valueOf(j));
                        } else {
                            data.append(String.valueOf(i)).append("-").append(String.valueOf(j));
                        }
                        result.add(data.toString());
                    }
                } else {
                    for (int j = 1; j <= 12; j++) {
                        StringBuilder data = new StringBuilder();
                        if (j < 10) {
                            data.append(String.valueOf(i)).append("-0").append(String.valueOf(j));
                        } else {
                            data.append(String.valueOf(i)).append("-").append(String.valueOf(j));
                        }
                        result.add(data.toString());
                    }
                }
            }
        }
        return result;
    }

    @Override
    public List<LogisticRelatedDto> getSelledCard(ReportQueryDto dto) {
        List<LogisticRelatedDto> result = new ArrayList<LogisticRelatedDto>();
        String sql = "select b.card_def_id,a.name,count(b.id) "
            + "from tm_crm_card_definition a,tm_member_package_info b "
            + "where a.status=1 and b.PURCHASE_DATE is not null and a.id=b.CARD_DEF_ID "
            + "and TO_DAYS(b.PURCHASE_DATE) >= TO_DAYS('" + dto.getTransactionStartDate() + "') "
            + "and TO_DAYS(b.PURCHASE_DATE) <= TO_DAYS('" + dto.getTransactionEndDate() + "') "
            + "and b.dealer_id in ('" + dto.getHierarchy() + "') " 
            + "group by b.card_def_id";
        List<Object[]> objectList = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectList == null || objectList.isEmpty()) {
            return result;
        }
        for (int i = 0; i < objectList.size(); i++) {
            Object[] resultCols = objectList.get(i);
            LogisticRelatedDto data = new LogisticRelatedDto();
            data.setId(Long.parseLong(Utils.getString(resultCols[0])));
            data.setCardName(Utils.getString(resultCols[1]));
            result.add(data);
        }
        return result;
    }

    @Override
    public String queryInventoryDataSelledForChart(ReportQueryDto dto,
        List<LogisticRelatedDto> dataList, List<String> months) throws AppException {
        StringBuilder result = new StringBuilder();
        int max = 0;
        for (int i = 0; i < months.size(); i++) {
            try {
                result.append("{")
                    .append(java.net.URLDecoder.decode("日期", LogisticConstants.ENCODE_UTF_8))
                    .append(":\"").append(months.get(i)).append("\",");
            } catch (UnsupportedEncodingException e) {
                LOGGER.error(e);
                throw new AppException("inventoryChartMonth", e);
            }
            for (int j = 0; j < dataList.size(); j++) {
                String sql = "select DATE_FORMAT(PURCHASE_DATE,'%Y-%m') months,card_def_id,count(id)"
                    + " from tm_member_package_info"
                    + " where PURCHASE_DATE is not null"
                    + " and dealer_id in('"
                    + dto.getHierarchy()
                    + "')"
                    + " and card_def_id='"
                    + dataList.get(j).getId()
                    + "'"
                    + " and DATE_FORMAT(PURCHASE_DATE,'%Y-%m')='"
                    + months.get(i)
                    + "'"
                    + " group by months,card_def_id"
                    + " order by months";
                List<Object[]> objectList = getJpaTemplate().executeFind(
                    new NativeQueryBySqlJpaCallback<Object[]>(sql));
                if (objectList != null && !objectList.isEmpty()) {
                    for (int k = 0; k < objectList.size(); k++) {
                        Object[] resultCols = objectList.get(k);
                        try {
                            if (Integer.parseInt(Utils.getString(resultCols[2])) > max) {
                                max = Integer.parseInt(Utils.getString(resultCols[2]));
                            }
                            result
                                .append("\"")
                                .append(
                                    java.net.URLDecoder.decode(dataList.get(j).getCardName(),
                                        LogisticConstants.ENCODE_UTF_8)).append("\":")
                                .append(Utils.getString(resultCols[2])).append(",");
                        } catch (UnsupportedEncodingException e) {
                            throw new AppException("inventoryChart", e);
                        }
                    }
                } else {
                    try {
                        result
                            .append("\"")
                            .append(
                                java.net.URLDecoder.decode(dataList.get(j).getCardName(),
                                    LogisticConstants.ENCODE_UTF_8)).append("\":").append("0")
                            .append(",");
                    } catch (UnsupportedEncodingException e) {
                        throw new AppException("inventoryChart", e);
                    }
                }
            }
            result.delete(result.length() - 1, result.length()).append("},");
        }
        max = (max / 10) * 10 + 10;
        return result.delete(result.length() - 1, result.length()).append("-MAXDATA-")
            .append(String.valueOf(max)).toString();
    }

    @Override
    public MemberReportDto getNewMember(ReportQueryDto dto) {
        MemberReportDto result = new MemberReportDto();
        String sql = "select DEALER_ID,count(1) FROM (select `a`.`MEMBER_NAME` AS `MEMBER_NAME`,"
            + "`a`.`MOBILE_PHONE` AS `MOBILE_PHONE`,min(`c`.`ACTIVE_DATE`) AS `TRANSACTION_DATE`,"
            + "`a`.`DEALER_ID` AS `DEALER_ID` "
            + "from (`tm_raw_member` `a` join `tm_member_package_info` `c`) "
            + "where (`a`.`ID` = `c`.`ACTIVE_MEMBER_ID`) "
            + "and (`c`.`status` in(2,3,4)) and (`c`.`MARS_LOCK_STATUS` = 0) "
            + "and (`a`.`DEALER_ID` in ('" + dto.getHierarchy() + "')) "
            + "group by `a`.`MEMBER_NAME`,`a`.`MOBILE_PHONE`,`a`.`DEALER_ID`) as member_info"
            + " where TO_DAYS(TRANSACTION_DATE) >= TO_DAYS('" + dto.getTransactionStartDate()+ "')"
            + " and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('"+ dto.getTransactionEndDate() + "')"
            + " group by DEALER_ID";
        List<Object[]> objectListNewMbr = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListNewMbr == null || objectListNewMbr.isEmpty()) {
            result.setDealerId(dto.getUserId());
            result.setPackageId(dto.getPackageId());
            result.setStartMonth(dto.getTransactionStartDate());
            result.setEndMonth(dto.getTransactionEndDate());
            result.setNewMember("0");
            result.setHierarchy(dto.getHierarchy());
        } else {
            Integer nMbr=0;
            for (int i = 0; i < objectListNewMbr.size(); i++) {
                Object[] resultColsNewMbr = objectListNewMbr.get(i);
                result.setDealerId(dto.getUserId());
                result.setPackageId(dto.getPackageId());
                result.setStartMonth(dto.getTransactionStartDate());
                result.setEndMonth(dto.getTransactionEndDate());
                nMbr = nMbr + Integer.parseInt(Utils.getString(resultColsNewMbr[1]));
                result.setHierarchy(dto.getHierarchy());
            }
            result.setNewMember(Integer.toString(nMbr));
        }
        return result;
    }

    @Override
    public MemberReportDto getNewPackage(MemberReportDto data) {
        String sql = "select dealer_id,count(1) from ((select `b`.`NAME` AS `PACKAGE_NAME`,"
            + "`c`.`PACKAGE_NUMBER` AS `PACKAGE_NUMBER`,`d`.`MEMBER_NAME` AS `MEMBER_NAME`,"
            + "`d`.`MOBILE_PHONE` AS `MOBILE_PHONE`,`e`.`CAR_PLATE` AS `CAR_PLATE`,"
            + "`c`.`PURCHASE_DATE` AS `PURCHASE_DATE`,`c`.`ACTIVE_DATE` AS `ACTIVE_DATE`,"
            + "`d`.`DEALER_ID` AS `DEALER_ID`,`c`.`PACKAGE_DEF_ID` AS `PACKAGE_DEF_ID` "
            + "from (((`tm_crm_package_definition` `b` join `tm_member_package_info` `c`) "
            + "join `tm_raw_member` `d`) join `tm_raw_car_plate` `e`) "
            + "where ((`b`.`ID` = `c`.`PACKAGE_DEF_ID`) and (`c`.`ACTIVE_MEMBER_ID` = `d`.`ID`)"
            + " and (`c`.`ACTIVE_CAR_ID` = `e`.`ID`) and (`c`.`DEALER_ID` = `d`.`DEALER_ID`))"
            + " and (d.DEALER_ID in ('" + data.getHierarchy() + "'))"
            + " and (`c`.`MARS_LOCK_STATUS` = 0) and (`c`.`status` in(2,3,4)))"
            + " union all "
            + "(select `b`.`NAME` AS `PACKAGE_NAME`,`c`.`PACKAGE_NUMBER` AS `PACKAGE_NUMBER`,"
            + "`d`.`MEMBER_NAME` AS `MEMBER_NAME`,`d`.`MOBILE_PHONE` AS `MOBILE_PHONE`,"
            + "`e`.`CAR_PLATE` AS `CAR_PLATE`,`c`.`PURCHASE_DATE` AS `PURCHASE_DATE`,"
            + "`c`.`ACTIVE_DATE` AS `ACTIVE_DATE`,`d`.`DEALER_ID` AS `DEALER_ID`,"
            + "`c`.`PACKAGE_DEF_ID` AS `PACKAGE_DEF_ID` from "
            + "(((`tm_crm_package_definition` `b` join `tm_member_package_info` `c`) "
            + "join `tm_raw_member` `d`) join `tm_raw_car_plate` `e`) "
            + "where ((`b`.`ID` = `c`.`PACKAGE_DEF_ID`) and (`c`.`status`=1) and (`c`.`MARS_LOCK_STATUS` = 0) "
            + "and (`c`.`MEMBER_ID` = `d`.`ID`) and (`c`.`CAR_ID` = `e`.`ID`) "
            + "and (`c`.`DEALER_ID` = `d`.`DEALER_ID`) and (d.DEALER_ID in ('" + data.getHierarchy() + "'))))"
            + " union all "
            /*+ "(select `b`.`NAME` AS `PACKAGE_NAME`,`c`.`PACKAGE_NUMBER` AS `PACKAGE_NUMBER`,"
            + "`d`.`MEMBER_NAME` AS `MEMBER_NAME`,`d`.`MOBILE_PHONE` AS `MOBILE_PHONE`,"
            + "`e`.`CAR_PLATE` AS `CAR_PLATE`,`c`.`PURCHASE_DATE` AS `PURCHASE_DATE`,"
            + "`c`.`ACTIVE_DATE` AS `ACTIVE_DATE`,`d`.`DEALER_ID` AS `DEALER_ID`,"
            + "`c`.`PACKAGE_DEF_ID` AS `PACKAGE_DEF_ID` from "
            + "(((`tm_crm_package_definition` `b` join `tm_member_package_info` `c`) "
            + "join `tm_raw_member` `d`) join `tm_raw_car_plate` `e`) "
            + "where ((`b`.`ID` = `c`.`PACKAGE_DEF_ID`) and (`c`.`status`=1) and (`c`.`MARS_LOCK_STATUS` = 1) "
            + "and (`c`.`MEMBER_ID` = `d`.`ID`) and (`c`.`CAR_ID` = `e`.`ID`)  AND (`b`.is_physical_card =1) "
            + "and (`c`.`DEALER_ID` = `d`.`DEALER_ID`) and (d.DEALER_ID in ('" + data.getHierarchy() + "'))))"
            + " UNION ALL "*/
            + "(SELECT `b`.`NAME` AS `PACKAGE_NAME`,`c`.`PACKAGE_NUMBER` AS `PACKAGE_NUMBER`,"
            + "`d`.`MEMBER_NAME` AS `MEMBER_NAME`,`d`.`MOBILE_PHONE` AS `MOBILE_PHONE`,'' AS `CAR_PLATE`,"
            + "`c`.`PURCHASE_DATE` AS `PURCHASE_DATE`,`c`.`ACTIVE_DATE` AS `ACTIVE_DATE`,`d`.`DEALER_ID` AS `DEALER_ID`,"
            + "`c`.`PACKAGE_DEF_ID` AS `PACKAGE_DEF_ID` FROM (((`tm_crm_package_definition` `b` JOIN `tm_member_package_info` `c`)"
            + " JOIN `tm_raw_member` `d`) ) WHERE ((`b`.`ID` = `c`.`PACKAGE_DEF_ID`) AND (`c`.`status`=1) AND ISNULL(`c`.`CAR_ID`)"
            + " AND (`c`.`MARS_LOCK_STATUS` = 0) AND (`c`.`MEMBER_ID` = `d`.`ID`) AND (`c`.`DEALER_ID` = `d`.`DEALER_ID`) "
            + " AND (d.DEALER_ID IN ('" + data.getHierarchy() + "'))))"
            /*+ " UNION ALL "
            + "(SELECT `b`.`NAME` AS `PACKAGE_NAME`,`c`.`PACKAGE_NUMBER` AS `PACKAGE_NUMBER`,`d`.`MEMBER_NAME` AS `MEMBER_NAME`,"
            + " `d`.`MOBILE_PHONE` AS `MOBILE_PHONE`,'' AS `CAR_PLATE`,`c`.`PURCHASE_DATE` AS `PURCHASE_DATE`,"
            + "`c`.`ACTIVE_DATE` AS `ACTIVE_DATE`, `d`.`DEALER_ID` AS `DEALER_ID`,`c`.`PACKAGE_DEF_ID` AS `PACKAGE_DEF_ID` "
            + "FROM (((`tm_crm_package_definition` `b` JOIN `tm_member_package_info` `c`) JOIN `tm_raw_member` `d`)) "
            + "WHERE ((`b`.`ID` = `c`.`PACKAGE_DEF_ID`) AND (`c`.`status`=1) AND (`c`.`MARS_LOCK_STATUS` = 1) "
            + "AND ISNULL(`c`.`CAR_ID`) AND (`c`.`MEMBER_ID` = `d`.`ID`) AND (`b`.is_physical_card =1) "
            + "AND (`c`.`DEALER_ID` = `d`.`DEALER_ID`) AND (d.DEALER_ID IN ('" + data.getHierarchy() + "'))))"*/
            + ") as member_packages"
            + " where TO_DAYS(PURCHASE_DATE) >= TO_DAYS('" + data.getStartMonth() + "')"
            + " and TO_DAYS(PURCHASE_DATE) <= TO_DAYS('" + data.getEndMonth() + "')";
        if (!"all".equals(data.getPackageId())) {
            sql = sql + " and PACKAGE_DEF_ID='" + data.getPackageId() + "'";
        }
        sql = sql + " group by dealer_id";
        List<Object[]> objectListNewPkg = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListNewPkg == null || objectListNewPkg.isEmpty()) {
            data.setNewPackage("0");
        } else {
            Integer nPkg=0;
            for (int i = 0; i < objectListNewPkg.size(); i++) {
                Object[] resultColsNewPkg = objectListNewPkg.get(i);
                nPkg = nPkg + Integer.parseInt(Utils.getString(resultColsNewPkg[1]));
            }
            data.setNewPackage(Integer.toString(nPkg));
        }
        return data;
    }

    @Override
    public MemberReportDto getNewCar(MemberReportDto data) {
        String sql = "select DEALER_ID,count(1) from "
            + "(select `b`.`MEMBER_NAME` AS `MEMBER_NAME`,`b`.`MOBILE_PHONE` AS `MOBILE_PHONE`,"
            + "`a`.`CAR_PLATE` AS `CAR_PLATE`,min(`c`.`ACTIVE_DATE`) AS `TRANSACTION_DATE`,"
            + "`b`.`DEALER_ID` AS `DEALER_ID` from ((`tm_raw_car_plate` `a` join `tm_raw_member` `b`)"
            + " join `tm_member_package_info` `c`) where ((`a`.`MEMBER_ID` = `b`.`ID`) "
            + "and (`c`.`ACTIVE_MEMBER_ID` = `b`.`ID`) and (`c`.`ACTIVE_CAR_ID` = `a`.`ID`) and "
            + "(`c`.`status` in(2,3,4)) and (`c`.`MARS_LOCK_STATUS` = 0)"
            + "and (`b`.`DEALER_ID` in ('" + data.getHierarchy() + "'))) "
            + "group by `b`.`MEMBER_NAME`,`b`.`MOBILE_PHONE`,`a`.`CAR_PLATE`,`b`.`DEALER_ID`) as member_cars "
            + " where TO_DAYS(TRANSACTION_DATE) >= TO_DAYS('" + data.getStartMonth() + "')"
            + " and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('" + data.getEndMonth() + "')"
            +" group by DEALER_ID";
        List<Object[]> objectListNewCr = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListNewCr == null || objectListNewCr.isEmpty()) {
            data.setNewCar("0");
        } else {
            Integer nCar = 0;
            for (int i = 0; i < objectListNewCr.size(); i++) {
                Object[] resultColsNewCr = objectListNewCr.get(i);
                nCar = nCar + Integer.parseInt(Utils.getString(resultColsNewCr[1]));
            }
            data.setNewCar(Integer.toString(nCar));
        }
        return data;
    }

    @Override
    public MemberReportDto getHistoryQty(MemberReportDto data) {
        String sql = "select DEALER_ID,member_qty,car_qty"
            + " from vt_history_qty "
            + "where DEALER_ID in('"
            + data.getHierarchy() + "') "
            + "and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS(NOW())"
            + " group by DEALER_ID";
        List<Object[]> objectListHis = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListHis == null || objectListHis.isEmpty()) {
            data.setAllMember("0");
            data.setAllCar("0");
        } else {
            Integer mbrQty = 0;
            Integer carQty = 0;
            for (int i = 0; i < objectListHis.size(); i++) {
                Object[] resultColsHis = objectListHis.get(i);
                mbrQty = mbrQty + Integer.parseInt(Utils.getString(resultColsHis[1]));
                carQty = carQty + Integer.parseInt(Utils.getString(resultColsHis[2]));
            }
            data.setAllMember(Integer.toString(mbrQty));
            data.setAllCar(Integer.toString(carQty));
        }
        return data;
    }

    @Override
    public MemberReportDto getHistoryPackageQty(MemberReportDto data) {
        String sql = "select DEALER_ID,count(1) from tm_member_package_info " + "where DEALER_ID in('"
            + data.getHierarchy() + "') "
            + "and TO_DAYS(CREATE_DATE) <= TO_DAYS(NOW()) and MARS_LOCK_STATUS = 0";
        if (!"all".equals(data.getPackageId())) {
            sql = sql + " and PACKAGE_DEF_ID='" + data.getPackageId() + "'";
        }
        sql=sql+" group by DEALER_ID";
        List<Object[]> objectListHisPkg = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListHisPkg == null || objectListHisPkg.isEmpty()) {
            data.setAllPackage("0");
        } else {
            Integer pkgQty = 0;
            for (int i = 0; i < objectListHisPkg.size(); i++) {
                Object[] resultColsHisPkg = objectListHisPkg.get(i);
                pkgQty = pkgQty + Integer.parseInt(Utils.getString(resultColsHisPkg[1]));
            }
            data.setAllPackage(Integer.toString(pkgQty));
        }
        return data;
    }

    @Override
    public List<MemberReportDetailsDto> getNewMemberDetails(ReportQueryDto dto) {
        List<MemberReportDetailsDto> result = new ArrayList<MemberReportDetailsDto>();
        String sql = "select member_name,mobile_phone,TRANSACTION_DATE "
            + "FROM (select `a`.`MEMBER_NAME` AS `MEMBER_NAME`,"
            + "`a`.`MOBILE_PHONE` AS `MOBILE_PHONE`,min(`c`.`ACTIVE_DATE`) AS `TRANSACTION_DATE`,"
            + "`a`.`DEALER_ID` AS `DEALER_ID` "
            + "from (`tm_raw_member` `a` join `tm_member_package_info` `c`) where ("
            + "(`a`.`ID` = `c`.`ACTIVE_MEMBER_ID`) and "
            + "(`c`.`status` in(2,3,4)) and (`c`.`MARS_LOCK_STATUS` = 0)) and "
            + "(`a`.`DEALER_ID` in ('" + dto.getHierarchy() + "')) "
            + "group by `a`.`MEMBER_NAME`,`a`.`MOBILE_PHONE`,`a`.`DEALER_ID`) as member_info"
            + " where TO_DAYS(TRANSACTION_DATE) >= TO_DAYS('" + dto.getTransactionStartDate()+ "')"
            + " and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('"+ dto.getTransactionEndDate() + "')"
            + " group by DEALER_ID,member_name,mobile_phone,TRANSACTION_DATE"
            + " order by TRANSACTION_DATE";
        List<Object[]> objectListNewMbr = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListNewMbr == null || objectListNewMbr.isEmpty()) {
            return result;
        } else {
            for (int i = 0; i < objectListNewMbr.size(); i++) {
                Object[] resultColsNewMbr = objectListNewMbr.get(i);
                MemberReportDetailsDto data = new MemberReportDetailsDto();
                data.setName(Utils.getString(resultColsNewMbr[0]));
                data.setTelephone(Utils.getString(resultColsNewMbr[1]));
                data.setCreateDate(Utils.getString(resultColsNewMbr[2]));
                result.add(data);
            }
        }
        return result;
    }

    @Override
    public List<MemberReportDetailsDto> getNewPackageDetails(ReportQueryDto dto) {
        List<MemberReportDetailsDto> result = new ArrayList<MemberReportDetailsDto>();
        String sql = "select PACKAGE_NAME,PACKAGE_NUMBER,MEMBER_NAME,MOBILE_PHONE,CAR_PLATE,"
            + "PURCHASE_DATE,ACTIVE_DATE "
            + "from ((select `b`.`NAME` AS `PACKAGE_NAME`,"
            + "`c`.`PACKAGE_NUMBER` AS `PACKAGE_NUMBER`,`d`.`MEMBER_NAME` AS `MEMBER_NAME`,"
            + "`d`.`MOBILE_PHONE` AS `MOBILE_PHONE`,`e`.`CAR_PLATE` AS `CAR_PLATE`,"
            + "`c`.`PURCHASE_DATE` AS `PURCHASE_DATE`,`c`.`ACTIVE_DATE` AS `ACTIVE_DATE`,"
            + "`d`.`DEALER_ID` AS `DEALER_ID`,`c`.`PACKAGE_DEF_ID` AS `PACKAGE_DEF_ID` "
            + "from (((`tm_crm_package_definition` `b` join `tm_member_package_info` `c`) "
            + "join `tm_raw_member` `d`) join `tm_raw_car_plate` `e`) "
            + "where ((`b`.`ID` = `c`.`PACKAGE_DEF_ID`) and (`c`.`ACTIVE_MEMBER_ID` = `d`.`ID`)"
            + " and (`c`.`ACTIVE_CAR_ID` = `e`.`ID`) and (`c`.`DEALER_ID` = `d`.`DEALER_ID`))"
            + " and (d.DEALER_ID in ('" + dto.getHierarchy() + "'))"
            + " and (`c`.`MARS_LOCK_STATUS` = 0) and (`c`.`status` in(2,3,4))) "
            + "union all "
            + "(select `b`.`NAME` AS `PACKAGE_NAME`,`c`.`PACKAGE_NUMBER` AS `PACKAGE_NUMBER`,"
            + "`d`.`MEMBER_NAME` AS `MEMBER_NAME`,`d`.`MOBILE_PHONE` AS `MOBILE_PHONE`,"
            + "`e`.`CAR_PLATE` AS `CAR_PLATE`,`c`.`PURCHASE_DATE` AS `PURCHASE_DATE`,"
            + "`c`.`ACTIVE_DATE` AS `ACTIVE_DATE`,`d`.`DEALER_ID` AS `DEALER_ID`,"
            + "`c`.`PACKAGE_DEF_ID` AS `PACKAGE_DEF_ID` from "
            + "(((`tm_crm_package_definition` `b` join `tm_member_package_info` `c`) "
            + "join `tm_raw_member` `d`) join `tm_raw_car_plate` `e`) "
            + "where ((`b`.`ID` = `c`.`PACKAGE_DEF_ID`) and (`c`.`status`=1) and (`c`.`MARS_LOCK_STATUS` = 0) "
            + "and (`c`.`MEMBER_ID` = `d`.`ID`) and (`c`.`CAR_ID` = `e`.`ID`) "
            + "and (`c`.`DEALER_ID` = `d`.`DEALER_ID`) and (d.DEALER_ID in ('" + dto.getHierarchy() + "'))))"
            + "union all "
            /*+ "(select `b`.`NAME` AS `PACKAGE_NAME`,`c`.`PACKAGE_NUMBER` AS `PACKAGE_NUMBER`,"
            + "`d`.`MEMBER_NAME` AS `MEMBER_NAME`,`d`.`MOBILE_PHONE` AS `MOBILE_PHONE`,"
            + "`e`.`CAR_PLATE` AS `CAR_PLATE`,`c`.`PURCHASE_DATE` AS `PURCHASE_DATE`,"
            + "`c`.`ACTIVE_DATE` AS `ACTIVE_DATE`,`d`.`DEALER_ID` AS `DEALER_ID`,"
            + "`c`.`PACKAGE_DEF_ID` AS `PACKAGE_DEF_ID` from "
            + "(((`tm_crm_package_definition` `b` join `tm_member_package_info` `c`) "
            + "join `tm_raw_member` `d`) join `tm_raw_car_plate` `e`) "
            + "where ((`b`.`ID` = `c`.`PACKAGE_DEF_ID`) and (`c`.`status`=1) and (`c`.`MARS_LOCK_STATUS` = 1) "
            + "and (`c`.`MEMBER_ID` = `d`.`ID`) and (`c`.`CAR_ID` = `e`.`ID`)  AND (`b`.is_physical_card =1) "
            + "and (`c`.`DEALER_ID` = `d`.`DEALER_ID`) and (d.DEALER_ID in ('" + dto.getHierarchy() + "'))))"
            + " UNION ALL "*/
            + "(SELECT `b`.`NAME` AS `PACKAGE_NAME`,`c`.`PACKAGE_NUMBER` AS `PACKAGE_NUMBER`,"
            + "`d`.`MEMBER_NAME` AS `MEMBER_NAME`,`d`.`MOBILE_PHONE` AS `MOBILE_PHONE`,'' AS `CAR_PLATE`,"
            + "`c`.`PURCHASE_DATE` AS `PURCHASE_DATE`,`c`.`ACTIVE_DATE` AS `ACTIVE_DATE`,`d`.`DEALER_ID` AS `DEALER_ID`,"
            + "`c`.`PACKAGE_DEF_ID` AS `PACKAGE_DEF_ID` FROM (((`tm_crm_package_definition` `b` JOIN `tm_member_package_info` `c`)"
            + " JOIN `tm_raw_member` `d`) ) WHERE ((`b`.`ID` = `c`.`PACKAGE_DEF_ID`) AND (`c`.`status`=1) AND ISNULL(`c`.`CAR_ID`)"
            + " AND (`c`.`MARS_LOCK_STATUS` = 0) AND (`c`.`MEMBER_ID` = `d`.`ID`) AND (`c`.`DEALER_ID` = `d`.`DEALER_ID`) "
            + " AND (d.DEALER_ID IN ('" + dto.getHierarchy() + "'))))"
            /*+ " UNION ALL "
            + "(SELECT `b`.`NAME` AS `PACKAGE_NAME`,`c`.`PACKAGE_NUMBER` AS `PACKAGE_NUMBER`,`d`.`MEMBER_NAME` AS `MEMBER_NAME`,"
            + " `d`.`MOBILE_PHONE` AS `MOBILE_PHONE`,'' AS `CAR_PLATE`,`c`.`PURCHASE_DATE` AS `PURCHASE_DATE`,"
            + "`c`.`ACTIVE_DATE` AS `ACTIVE_DATE`, `d`.`DEALER_ID` AS `DEALER_ID`,`c`.`PACKAGE_DEF_ID` AS `PACKAGE_DEF_ID` "
            + "FROM (((`tm_crm_package_definition` `b` JOIN `tm_member_package_info` `c`)"
            + "  JOIN `tm_raw_member` `d`)) WHERE ((`b`.`ID` = `c`.`PACKAGE_DEF_ID`) AND (`c`.`status`=1) "
            + "AND (`c`.`MARS_LOCK_STATUS` = 1) AND ISNULL(`c`.`CAR_ID`) AND (`c`.`MEMBER_ID` = `d`.`ID`) "
            + "AND (`b`.is_physical_card =1) AND (`c`.`DEALER_ID` = `d`.`DEALER_ID`) AND (d.DEALER_ID IN ('" + dto.getHierarchy() + "'))))"*/
            + ") as member_packages "
            + "where TO_DAYS(PURCHASE_DATE) >= TO_DAYS('" + dto.getTransactionStartDate() + "') " 
            + "and TO_DAYS(PURCHASE_DATE) <= TO_DAYS('" + dto.getTransactionEndDate() + "') ";
        if (!"all".equals(dto.getPackageId())) {
            sql = sql + " and PACKAGE_DEF_ID='" + dto.getPackageId() + "'";
        }
        sql = sql + " order by PURCHASE_DATE";
        List<Object[]> objectListNewPkg = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListNewPkg == null || objectListNewPkg.isEmpty()) {
            return result;
        } else {
            for (int i = 0; i < objectListNewPkg.size(); i++) {
                Object[] resultColsNewPkg = objectListNewPkg.get(i);
                MemberReportDetailsDto data = new MemberReportDetailsDto();
                data.setPackageName(Utils.getString(resultColsNewPkg[0]));
                data.setPackageNo(Utils.getString(resultColsNewPkg[1]));
                data.setName(Utils.getString(resultColsNewPkg[2]));
                data.setTelephone(Utils.getString(resultColsNewPkg[3]));
                data.setCreateDate(Utils.getString(resultColsNewPkg[5]));
                if (Utils.isNullOrEmpty(Utils.getString(resultColsNewPkg[6]))) {
                    data.setActiveStatus("未激活");
                    data.setCarPlate("");
                } else {
                    data.setActiveStatus("已激活");
                    data.setCarPlate(Utils.getString(resultColsNewPkg[4]));
                }
                result.add(data);
            }
        }
        return result;
    }

    @Override
    public List<MemberReportDetailsDto> getNewCarDetails(ReportQueryDto dto) {
        List<MemberReportDetailsDto> result = new ArrayList<MemberReportDetailsDto>();
        String sql = "select MEMBER_NAME,MOBILE_PHONE,CAR_PLATE,TRANSACTION_DATE "
            + "from (select `b`.`MEMBER_NAME` AS `MEMBER_NAME`,`b`.`MOBILE_PHONE` AS `MOBILE_PHONE`,"
            + "`a`.`CAR_PLATE` AS `CAR_PLATE`,min(`c`.`ACTIVE_DATE`) AS `TRANSACTION_DATE`,"
            + "`b`.`DEALER_ID` AS `DEALER_ID` from ((`tm_raw_car_plate` `a` join `tm_raw_member` `b`)"
            + " join `tm_member_package_info` `c`) where ((`a`.`MEMBER_ID` = `b`.`ID`) "
            + "and (`c`.`ACTIVE_MEMBER_ID` = `b`.`ID`) and (`c`.`ACTIVE_CAR_ID` = `a`.`ID`) and "
            + "(`c`.`status` in(2,3,4)) and (`c`.`MARS_LOCK_STATUS` = 0)"
            + "and (`b`.`DEALER_ID` in ('" + dto.getHierarchy() + "'))) "
            + "group by `b`.`MEMBER_NAME`,`b`.`MOBILE_PHONE`,`a`.`CAR_PLATE`,`b`.`DEALER_ID`) as member_cars "
            + "where TO_DAYS(TRANSACTION_DATE) >= TO_DAYS('" + dto.getTransactionStartDate() + "') "
            + "and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('" + dto.getTransactionEndDate() + "') "
            + "order by TRANSACTION_DATE";
        List<Object[]> objectListNewCr = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListNewCr == null || objectListNewCr.isEmpty()) {
            return result;
        } else {
            for (int i = 0; i < objectListNewCr.size(); i++) {
                Object[] resultColsNewCr = objectListNewCr.get(i);
                MemberReportDetailsDto data = new MemberReportDetailsDto();
                data.setName(Utils.getString(resultColsNewCr[0]));
                data.setTelephone(Utils.getString(resultColsNewCr[1]));
                data.setCarPlate(Utils.getString(resultColsNewCr[2]));
                data.setCreateDate(Utils.getString(resultColsNewCr[3]));
                result.add(data);
            }
        }
        return result;
    }

    @Override
    public MemberChartDto queryMemberDataForChart(ReportQueryDto dto, String month) {
        MemberChartDto result = new MemberChartDto();
        String sql = "select date_format(`TRANSACTION_DATE`,'%Y-%m') AS `months`,count(1) "
            + "from (select `a`.`MEMBER_NAME` AS `MEMBER_NAME`,`a`.`MOBILE_PHONE` AS `MOBILE_PHONE`,"
            + "`b`.`CAR_PLATE` AS `CAR_PLATE`,min(`c`.`ACTIVE_DATE`) AS `TRANSACTION_DATE`,`a`.`DEALER_ID` AS `DEALER_ID` "
            + "from ((`tm_raw_member` `a` join `tm_raw_car_plate` `b`) join `tm_member_package_info` `c`) "
            + "where ((`a`.`ID` = `b`.`MEMBER_ID`) and (`a`.`ID` = `c`.`ACTIVE_MEMBER_ID`) and (`b`.`ID` = `c`.`ACTIVE_CAR_ID`) "
            + "and (`c`.`status` in(2,3,4)) and (`c`.`MARS_LOCK_STATUS` = 0)) "
            + "and `a`.`DEALER_ID` in('" + dto.getHierarchy() + "') "
            + "group by `a`.`MEMBER_NAME`,`a`.`MOBILE_PHONE`,`b`.`CAR_PLATE`,`a`.`DEALER_ID`) as member_details " 
            + "where date_format(`TRANSACTION_DATE`,'%Y-%m')='" + month + "' group by date_format(`TRANSACTION_DATE`,'%Y-%m')";
        List<Object[]> objectListNewMbr = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListNewMbr == null || objectListNewMbr.isEmpty()) {
            result.setMonth(month);
            result.setNewMember("0");
        } else {
            for (int i = 0; i < objectListNewMbr.size(); i++) {
                Object[] resultColsNewCr = objectListNewMbr.get(i);
                result.setMonth(month);
                result.setNewMember(Utils.getString(resultColsNewCr[1]));
            }
        }
        return result;
    }

    @Override
    public MemberChartDto queryPackageDataForChart
            (MemberChartDto dto, String userId, String packageId) {
        String sql = "select months,sum(qty) "
            + "from (select date_format(`tm_member_package_info`.`PURCHASE_DATE`,'%Y-%m') AS `months`,"
            + "count(`tm_member_package_info`.`ID`) AS `qty`,"
            + "`tm_member_package_info`.`DEALER_ID` AS `DEALER_ID`,"
            + "`tm_member_package_info`.`PACKAGE_DEF_ID` AS `PACKAGE_DEF_ID` "
            + "from `tm_member_package_info` where `tm_member_package_info`.`DEALER_ID` in('"+ userId + "') "
            + "group by date_format(`tm_member_package_info`.`PURCHASE_DATE`,'%Y-%m'),"
            + "`tm_member_package_info`.`DEALER_ID`) as package_details " 
            + "where months='" + dto.getMonth() + "'";
        if (!"all".equals(packageId)) {
            sql = sql + " and PACKAGE_DEF_ID='" + packageId + "'";
        }
        sql = sql + " group by months";
        List<Object[]> objectListAccMbr = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListAccMbr == null || objectListAccMbr.isEmpty()) {
            dto.setNewPackage("0");
        } else {
            for (int i = 0; i < objectListAccMbr.size(); i++) {
                dto.setNewPackage(Utils.getString(objectListAccMbr.get(i)[1]));
            }
        }
        return dto;
    }

    @Override
    public MemberChartDto queryCarDataForChart(MemberChartDto dto, String userId) {
        String sql = "select date_format(`car_details`.`TRANSACTION_DATE`,'%Y-%m') AS `months`,count(1) "
            + "from (select `b`.`MEMBER_NAME` AS `MEMBER_NAME`,`b`.`MOBILE_PHONE` AS `MOBILE_PHONE`,"
            + "`a`.`CAR_PLATE` AS `CAR_PLATE`,min(`c`.`ACTIVE_DATE`) AS `TRANSACTION_DATE`,`b`.`DEALER_ID` AS `DEALER_ID` "
            + "from ((`tm_raw_car_plate` `a` join `tm_raw_member` `b`) join `tm_member_package_info` `c`) "
            + "where ((`a`.`MEMBER_ID` = `b`.`ID`) and (`c`.`ACTIVE_MEMBER_ID` = `b`.`ID`) "
            + "and (`c`.`ACTIVE_CAR_ID` = `a`.`ID`) and (`c`.`status` in(2,3,4)) and (`c`.`MARS_LOCK_STATUS` = 0)) "
            + "and `b`.`DEALER_ID` in('" + userId + "') "
            + "group by `b`.`MEMBER_NAME`,`b`.`MOBILE_PHONE`,`a`.`CAR_PLATE`,`b`.`DEALER_ID`) as car_details "
            + "where date_format(`car_details`.`TRANSACTION_DATE`,'%Y-%m')='" + dto.getMonth() + "' "
            + "group by date_format(`car_details`.`TRANSACTION_DATE`,'%Y-%m')";
        List<Object[]> objectListAccMbr = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListAccMbr == null || objectListAccMbr.isEmpty()) {
            dto.setNewCar("0");
        } else {
            for (int i = 0; i < objectListAccMbr.size(); i++) {
                Object[] resultColsNewCr = objectListAccMbr.get(i);
                dto.setNewCar(Utils.getString(resultColsNewCr[1]));
            }
        }
        return dto;
    }

    @Override
    public MemberChartDto queryAccumulateMemberDataForChart(MemberChartDto dto, String userId) {
        String sql = "select DATE_FORMAT(CREATE_DATE,'%Y-%m') months,count(id) "
            + "from tm_raw_member " + "where dealer_id in('"
            + userId + "') "
            + "and DATE_FORMAT(CREATE_DATE,'%Y-%m')<='" + dto.getMonth() + "'";
        List<Object[]> objectListAccMbr = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListAccMbr == null || objectListAccMbr.isEmpty()) {
            // dto.setAccumulateMember("0")
        } else {
            for (int i = 0; i < objectListAccMbr.size(); i++) {
                //Object[] resultColsNewCr = objectListAccMbr.get(i)
                // dto.setAccumulateMember(Utils.getString(resultColsNewCr[1]))
            }
        }
        return dto;
    }

    @Override
    public SalesReportDto getSalesVolume(ReportQueryDto dto,String dealerNo){
        SalesReportDto result = new SalesReportDto();
        LOGGER.info("handleSalesVolume Start ------>" + new Date());
        SessionImplementor session = getEntityManager().unwrap(
                SessionImplementor.class);
        Connection conn = session.connection();
        CallableStatement sc = null;
        try {
            sc = conn.prepareCall("{call prc_sales_report(?,?,?,?,?,?,?,?,?)}");
            sc.setString(1, dealerNo);
            sc.setString(2, dto.getTransactionStartDate());
            sc.setString(3, dto.getTransactionEndDate());
            sc.setInt(4, Integer.valueOf(dto.getPackageId()));
            sc.registerOutParameter(5, Types.DOUBLE);
            sc.registerOutParameter(6, Types.DOUBLE);
            sc.registerOutParameter(7, Types.DOUBLE);
            sc.registerOutParameter(8, Types.DOUBLE);
            sc.registerOutParameter(9, Types.DOUBLE);
            sc.execute();
            LOGGER.info("handleSalesVolume end :------>"
                    + dealerNo + "      " + new Date());
            result.setDealerId(dto.getUserId());
            result.setPackageId(dto.getPackageId());
            result.setStartMonth(dto.getTransactionStartDate());
            result.setEndMonth(dto.getTransactionEndDate());
            result.setSalesPrePay(sc.getDouble(5));
            result.setUsedValue(sc.getDouble(6));
            result.setSalvageValue(sc.getDouble(7));
            result.setActualSalesVolume(sc.getDouble(8));
            result.setAllNotUsedValue(sc.getDouble(9));
        } catch (SQLException e) {
            LOGGER.error(e);
        } finally {
            try {
                if (sc != null) {
                    sc.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOGGER.error(e);
            }
        }
        return result;
    }

    @Override
    public SalesReportDto getSalesReport(ReportQueryDto dto,String dealerNo){
        SalesReportDto result = new SalesReportDto();
        LOGGER.info("handleSalesVolume Start ------>" + new Date());
        SessionImplementor session = getEntityManager().unwrap(
                SessionImplementor.class);
        Connection conn = session.connection();
        CallableStatement sc = null;
        try {
            sc = conn.prepareCall("{call prc_salesreport_accumulated(?,?,?,?,?,?,?,?)}");
            sc.setString(1, dealerNo);
            sc.setInt(2, Integer.valueOf(dto.getPackageId()));
            sc.registerOutParameter(3, Types.DOUBLE);
            sc.registerOutParameter(4, Types.DOUBLE);
            sc.registerOutParameter(5, Types.DOUBLE);
            sc.registerOutParameter(6, Types.DOUBLE);
            sc.registerOutParameter(7, Types.DOUBLE);
            sc.registerOutParameter(8, Types.DOUBLE);
            sc.execute();
            LOGGER.info("handleSalesVolume end :------>"
                    + dealerNo + "      " + new Date());
            result.setDealerId(dto.getUserId());
            result.setPackageId(dto.getPackageId());
            result.setPrePay(sc.getDouble(3));
            result.setUsedPrePay(sc.getDouble(4));
            result.setNotUsedPrePay(sc.getDouble(5));
            result.setExpiredPrePay(sc.getDouble(6));
            result.setIncomePrePay(sc.getDouble(7));
            result.setIncomeNotPrePay(sc.getDouble(8));
        } catch (SQLException e) {
            LOGGER.error(e);
        } finally {
            try {
                if (sc != null) {
                    sc.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOGGER.error(e);
            }
        }
        return result;
    }

    @Override
    public List<SalesReportDetailsDto> exportSalesReport(ReportQueryDto salesDto){
        List<SalesReportDetailsDto> result = new ArrayList<SalesReportDetailsDto>();
        String table = "vt_period_prepay_details";
        if("1".equals(salesDto.getType())){
            table = "vt_period_prepay_details";
        } else if("2".equals(salesDto.getType())){
            table = "vt_period_used_details";
        } else if("3".equals(salesDto.getType())){
            table = "vt_period_not_used_details";
        } else if("4".equals(salesDto.getType())){
            table = "vt_period_expired_details";
        } else if("5".equals(salesDto.getType())){
            table = "vt_period_income_details";
        } else if("6".equals(salesDto.getType())){
            table = "vt_period_income_not_prepay_details";
        }
        
        String sql = "select member_name,mobile_phone,CAR_PLATE,TRANSACTION_DATE,package_number,package_name,balance "
                + "from " + table + " "
                + "where DEALER_ID in('" + salesDto.getHierarchy() + "') ";
            if (!"-1".equals(salesDto.getPackageId())) {
                sql = sql + " and PACKAGE_DEF_ID='" + salesDto.getPackageId() + "'";
                }
            sql = sql + " order by TRANSACTION_DATE";
            List<Object[]> objectListPrepay = getJpaTemplate().executeFind(
                new NativeQueryBySqlJpaCallback<Object[]>(sql));
            if (objectListPrepay == null || objectListPrepay.isEmpty()) {
                return result;
            } else {
                for (int i = 0; i < objectListPrepay.size(); i++) {
                    Object[] resultColsNewMbr = objectListPrepay.get(i);
                    SalesReportDetailsDto data = new SalesReportDetailsDto();
                    data.setName(Utils.getString(resultColsNewMbr[0]));
                    data.setTelephone(Utils.getString(resultColsNewMbr[1]));
                    data.setCarPlate(Utils.getString(resultColsNewMbr[2]));
                    data.setTransactionDate(Utils.getString(resultColsNewMbr[3]));
                    data.setPackageNo(Utils.getString(resultColsNewMbr[4]));
                    data.setPackageName(Utils.getString(resultColsNewMbr[5]));
                    data.setBalance(Utils.getString(resultColsNewMbr[6]));
                    result.add(data);
                }
            }
        return result;
    }
    
    @Override
    public SalesReportDto getPeriodPrePay(ReportQueryDto dto){
        SalesReportDto result = new SalesReportDto();
        String sql = "select DEALER_ID,sum(balance) from vt_period_prepay"
            + " where DEALER_ID in('" + dto.getHierarchy() + "')"
            + " and TO_DAYS(TRANSACTION_DATE) >= TO_DAYS('" + dto.getTransactionStartDate()+ "')"
            + " and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('" + dto.getTransactionEndDate() + "')";
        if (!"-1".equals(dto.getPackageId())) {
            sql = sql + " and PACKAGE_DEF_ID = '" + dto.getPackageId() + "'";
            }
        sql = sql + " group by dealer_id";
        List<Object[]> objectListPrepay = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListPrepay == null || objectListPrepay.isEmpty()) {
            result.setDealerId(dto.getUserId());
            result.setPackageId(dto.getPackageId());
            result.setStartMonth(dto.getTransactionStartDate());
            result.setEndMonth(dto.getTransactionEndDate());
            result.setSalesPrePay(0);
            result.setHierarchy(dto.getHierarchy());
            } else {
                double prePay = 0;
                for (int i = 0; i < objectListPrepay.size(); i++) {
                    Object[] resultColsPrepay = objectListPrepay.get(i);
                    result.setDealerId(dto.getUserId());
                    result.setPackageId(dto.getPackageId());
                    result.setStartMonth(dto.getTransactionStartDate());
                    result.setEndMonth(dto.getTransactionEndDate());
                    result.setHierarchy(dto.getHierarchy());
                    prePay = prePay + Double.parseDouble(Utils.getString(resultColsPrepay[1]));
                    }
                result.setSalesPrePay(prePay);
                }
        return result;
        }

    @Override
    public SalesReportDto getPeriodUsed(SalesReportDto data){
        String sql = "select DEALER_ID,sum(balance) from vt_period_used"
            + " where DEALER_ID in('" + data.getHierarchy() + "')" 
            + " and TO_DAYS(TRANSACTION_DATE) >= TO_DAYS('"+ data.getStartMonth() + "')" 
            + " and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('"+ data.getEndMonth() + "')";
        if (!"-1".equals(data.getPackageId())) {
            sql = sql + " and PACKAGE_DEF_ID='" + data.getPackageId() + "'";
            }
        sql = sql + " group by dealer_id";
        List<Object[]> objectListUsed = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListUsed == null || objectListUsed.isEmpty()) {
            data.setUsedValue(0);
            } else {
                double usedValue=0;
            for (int i = 0; i < objectListUsed.size(); i++) {
                Object[] resultColsUsed = objectListUsed.get(i);
                usedValue = usedValue + (Double.parseDouble(Utils.getString(resultColsUsed[1])));            
                }
            data.setUsedValue(usedValue);
            }
        return data;
        }
    @Override
    public SalesReportDto getPeriodExpired(SalesReportDto data){
        String sql = "select DEALER_ID,sum(balance) from vt_period_expired"
            + " where DEALER_ID in('" + data.getHierarchy() + "')" 
            + " and TO_DAYS(TRANSACTION_DATE) >= TO_DAYS('"+ data.getStartMonth() + "')" 
            + " and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('"+ data.getEndMonth() + "')";
        if (!"-1".equals(data.getPackageId())) {
            sql = sql + " and PACKAGE_DEF_ID='" + data.getPackageId() + "'";
            }
        sql = sql + " group by dealer_id";
        List<Object[]> objectListExpired = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListExpired == null || objectListExpired.isEmpty()) {
            data.setSalvageValue(0);
            } else {
                double expireValue=0;
                for (int i = 0; i < objectListExpired.size(); i++) {
                    Object[] resultColsNewPkg = objectListExpired.get(i);
                    expireValue=expireValue+Double.parseDouble(Utils.getString(resultColsNewPkg[1]));
                    }
                data.setSalvageValue(expireValue); 
                }
        return data;
        }

    @Override
    public SalesReportDto getPeriodIncome(SalesReportDto data){
        String sql = "select dealer_id,sum(balance) from vt_period_income"
            + " where DEALER_ID in('" + data.getHierarchy() + "')" 
            + " and TO_DAYS(TRANSACTION_DATE) >= TO_DAYS('"+ data.getStartMonth() + "')" 
            + " and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('"+ data.getEndMonth() + "')";
        if (!"-1".equals(data.getPackageId())) {
            sql = sql + " and PACKAGE_DEF_ID='" + data.getPackageId() + "'";
            }
        sql = sql + " group by dealer_id";
        List<Object[]> objectListIncome = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListIncome == null || objectListIncome.isEmpty()) {
            data.setActualSalesVolume(data.getUsedValue()+data.getSalvageValue());
            } else {
                double inComeValue=0;
                for (int i = 0; i < objectListIncome.size(); i++) {
                    Object[] resultColsNewPkg = objectListIncome.get(i);
                    inComeValue=inComeValue+Double.parseDouble(Utils.getString(resultColsNewPkg[1]));
                    }
                inComeValue=inComeValue+data.getUsedValue()+data.getSalvageValue();
                data.setActualSalesVolume(inComeValue);
                }
        return data;
        }
    
    @Override
    public SalesReportDto getAccumulatePrePay(SalesReportDto data){
        String sql = "select dealer_id,sum(balance) from vt_accumulate_prepay"
            + " where DEALER_ID in('" + data.getHierarchy() + "')"
                + " and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('"+ data.getEndMonth() + "')";
        if (!"-1".equals(data.getPackageId())) {
            sql = sql + " and PACKAGE_DEF_ID = '" + data.getPackageId() + "'";
            }
        sql = sql + " group by dealer_id";
        List<Object[]> objectListPrepay = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListPrepay == null || objectListPrepay.isEmpty()) {
            data.setAllPrePay(0);
            } else {
                double allPrepay = 0;
                for (int i = 0; i < objectListPrepay.size(); i++) {
                    Object[] resultColsPrepay = objectListPrepay.get(i);
                    allPrepay = allPrepay + (Double.parseDouble(Utils.getString(resultColsPrepay[1])));
                    }
                data.setAllPrePay(allPrepay);
                }
        return data;
        }

    @Override
    public SalesReportDto getAccumulateUsed(SalesReportDto data){
        String sql = "select dealer_id,sum(balance) from vt_accumulate_used"
            + " where DEALER_ID in('" + data.getHierarchy() + "')"
                + " and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('"+ data.getEndMonth() + "')";
        if (!"-1".equals(data.getPackageId())) {
            sql = sql + " and PACKAGE_DEF_ID='" + data.getPackageId() + "'";
            }
        sql = sql + " group by dealer_id";
        List<Object[]> objectListAccUsed = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListAccUsed == null || objectListAccUsed.isEmpty()) {
            data.setAllUsedValue(0);
            } else {
                double accUsedValue=0;
            for (int i = 0; i < objectListAccUsed.size(); i++) {
                Object[] resultColsUsed = objectListAccUsed.get(i);
                accUsedValue = accUsedValue + (Double.parseDouble(Utils.getString(resultColsUsed[1])));                
                }
            data.setAllUsedValue(accUsedValue);
                }
        return data;
        }

    @Override
    public SalesReportDto getAccumulateExpired(SalesReportDto data){
        String sql = "select dealer_id,sum(balance) from vt_accumulate_expired"
            + " where DEALER_ID in('" + data.getHierarchy() + "')"
                + " and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('"+ data.getEndMonth() + "')";
        if (!"-1".equals(data.getPackageId())) {
            sql = sql + " and PACKAGE_DEF_ID='" + data.getPackageId() + "'";
            }
        sql = sql + " group by dealer_id";
        List<Object[]> objectListAcExpired = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListAcExpired == null || objectListAcExpired.isEmpty()) {
            data.setAllSalvageValue(0);
            data.setAllSalesVolume(data.getAllUsedValue()+data.getAllSalvageValue());
            } else {
                double allExpireValue=0;
                for (int i = 0; i < objectListAcExpired.size(); i++) {
                    Object[] resultColsNewPkg = objectListAcExpired.get(i);
                    allExpireValue=allExpireValue+Double.parseDouble(Utils.getString(resultColsNewPkg[1]));
                    }
                data.setAllSalvageValue(allExpireValue);
                data.setAllSalesVolume(data.getAllUsedValue()+data.getAllSalvageValue()); 
                }
        return data;
        }


    @Override
    public SalesReportDto getAccumulateIncome(SalesReportDto data){
        String sql = "select dealer_id,sum(balance) from vt_accumulate_income"
            + " where DEALER_ID in('" + data.getHierarchy() + "')"
                + " and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('"+ data.getEndMonth() + "')";
        if (!"-1".equals(data.getPackageId())) {
            sql = sql + " and PACKAGE_DEF_ID='" + data.getPackageId() + "'";
            }
        sql = sql + " group by dealer_id";
        List<Object[]> objectListAcIncome = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListAcIncome == null || objectListAcIncome.isEmpty()) {
            data.setAllSalesVolume(data.getAllUsedValue()+data.getAllSalvageValue());
            } else {
                double allInComeValue=0;
                for (int i = 0; i < objectListAcIncome.size(); i++) {
                    Object[] resultColsNewPkg = objectListAcIncome.get(i);
                    allInComeValue=allInComeValue+Double.parseDouble(Utils.getString(resultColsNewPkg[1]));
                    }
                allInComeValue=allInComeValue+data.getAllUsedValue()+data.getAllSalvageValue();
                data.setAllSalesVolume(allInComeValue);
                }
        return data;
        }

    @Override
    public SalesReportDto getAccumulateNotUsed(SalesReportDto data){
        String sql = "select dealer_id,sum(balance) from vt_accumulate_not_used"
                + " where DEALER_ID in('" + data.getHierarchy() + "')"
                + " and DATE_FORMAT(TRANSACTION_DATE,'%Y%m%d') <= DATE_FORMAT(NOW(),'%Y%m%d')";
        if (!"-1".equals(data.getPackageId())) {
            sql = sql + " and PACKAGE_DEF_ID='" + data.getPackageId() + "'";
            }
        sql = sql + " group by dealer_id";
        List<Object[]> objectListAcIncome = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListAcIncome == null || objectListAcIncome.isEmpty()) {
            data.setAllNotUsedValue(0);
            } else {
                double allNotUsedValue=0;
                for (int i = 0; i < objectListAcIncome.size(); i++) {
                    Object[] resultColsNewPkg = objectListAcIncome.get(i);
                    allNotUsedValue=allNotUsedValue+Double.parseDouble(Utils.getString(resultColsNewPkg[1]));
                    }
                data.setAllNotUsedValue(allNotUsedValue);
                }
        return data;
        }
    
    @Override
    public List<SalesReportDetailsDto> getPeriodPrePayDetails(ReportQueryDto dto){
        List<SalesReportDetailsDto> result = new ArrayList<SalesReportDetailsDto>();
        String sql = "select member_name,mobile_phone,CAR_PLATE,TRANSACTION_DATE,package_number,package_name,balance "
            + "from vt_period_prepay_details "
            + "where DEALER_ID in('" + dto.getHierarchy() + "') "
            + "and TO_DAYS(TRANSACTION_DATE) >= TO_DAYS('" + dto.getTransactionStartDate() + "') "
            + "and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('" + dto.getTransactionEndDate() + "')";
        if (!"-1".equals(dto.getPackageId())) {
            sql = sql + " and PACKAGE_DEF_ID='" + dto.getPackageId() + "'";
            }
        sql = sql + " order by TRANSACTION_DATE";
        List<Object[]> objectListPrepay = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListPrepay == null || objectListPrepay.isEmpty()) {
            return result;
        } else {
            for (int i = 0; i < objectListPrepay.size(); i++) {
                Object[] resultColsNewMbr = objectListPrepay.get(i);
                SalesReportDetailsDto data = new SalesReportDetailsDto();
                data.setName(Utils.getString(resultColsNewMbr[0]));
                data.setTelephone(Utils.getString(resultColsNewMbr[1]));
                data.setCarPlate(Utils.getString(resultColsNewMbr[2]));
                data.setTransactionDate(Utils.getString(resultColsNewMbr[3]));
                data.setPackageNo(Utils.getString(resultColsNewMbr[4]));
                data.setPackageName(Utils.getString(resultColsNewMbr[5]));
                data.setBalance(Utils.getString(resultColsNewMbr[6]));
                result.add(data);
            }
        }
        return result;
    }
    @Override
    public List<SalesReportDetailsDto> getPeriodUsedDetails(ReportQueryDto dto){
        List<SalesReportDetailsDto> result = new ArrayList<SalesReportDetailsDto>();
        String sql = "select member_name,mobile_phone,CAR_PLATE,TRANSACTION_DATE,package_number,package_name,balance "
            + "from vt_period_used_details "
            + "where DEALER_ID in('" + dto.getHierarchy() + "') "
            + "and TO_DAYS(TRANSACTION_DATE) >= TO_DAYS('" + dto.getTransactionStartDate() + "') "
            + "and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('" + dto.getTransactionEndDate() + "')" ;
            if (!"-1".equals(dto.getPackageId())) {
                sql = sql + " and PACKAGE_DEF_ID='" + dto.getPackageId() + "'";
                }
            sql = sql + " order by TRANSACTION_DATE";
        List<Object[]> objectListUsed = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListUsed == null || objectListUsed.isEmpty()) {
            return result;
        } else {
            for (int i = 0; i < objectListUsed.size(); i++) {
                Object[] resultColsUsed = objectListUsed.get(i);
                SalesReportDetailsDto data = new SalesReportDetailsDto();
                data.setName(Utils.getString(resultColsUsed[0]));
                data.setTelephone(Utils.getString(resultColsUsed[1]));
                data.setCarPlate(Utils.getString(resultColsUsed[2]));
                data.setTransactionDate(Utils.getString(resultColsUsed[3]));
                data.setPackageNo(Utils.getString(resultColsUsed[4]));
                data.setPackageName(Utils.getString(resultColsUsed[5]));
                data.setBalance(Utils.getString(resultColsUsed[6]));
                result.add(data);
            }
        }
        return result;
    }
    @Override
    public List<SalesReportDetailsDto> getPeriodExpiredDetails(ReportQueryDto dto){
        List<SalesReportDetailsDto> result = new ArrayList<SalesReportDetailsDto>();
        String sql = "select member_name,mobile_phone,CAR_PLATE,TRANSACTION_DATE,package_number,package_name,balance "
            + "from vt_period_expired_details "
            + "where DEALER_ID in('" + dto.getHierarchy() + "') "
            + "and TO_DAYS(TRANSACTION_DATE) >= TO_DAYS('" + dto.getTransactionStartDate() + "') "
            + "and TO_DAYS(TRANSACTION_DATE) <= TO_DAYS('" + dto.getTransactionEndDate() + "')";
            if (!"-1".equals(dto.getPackageId())) {
                sql = sql + " and PACKAGE_DEF_ID='" + dto.getPackageId() + "'";
                }
            sql = sql + " order by TRANSACTION_DATE";
        List<Object[]> objectListPrepay = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListPrepay == null || objectListPrepay.isEmpty()) {
            return result;
        } else {
            for (int i = 0; i < objectListPrepay.size(); i++) {
                Object[] resultColsNewMbr = objectListPrepay.get(i);
                SalesReportDetailsDto data = new SalesReportDetailsDto();
                data.setName(Utils.getString(resultColsNewMbr[0]));
                data.setTelephone(Utils.getString(resultColsNewMbr[1]));
                data.setCarPlate(Utils.getString(resultColsNewMbr[2]));
                data.setTransactionDate(Utils.getString(resultColsNewMbr[3]));
                data.setPackageNo(Utils.getString(resultColsNewMbr[4]));
                data.setPackageName(Utils.getString(resultColsNewMbr[5]));
                data.setBalance(Utils.getString(resultColsNewMbr[6]));
                result.add(data);
            }
        }
        return result;
    }

    @Override
    public List<SalesReportDetailsDto> getAccumulateNotUsedDetails(ReportQueryDto dto){
        List<SalesReportDetailsDto> result = new ArrayList<SalesReportDetailsDto>();
        String sql = "select member_name,mobile_phone,CAR_PLATE,TRANSACTION_DATE,package_number,package_name,balance "
            + "from vt_accumulate_not_used_details "
            + "where DEALER_ID in('" + dto.getHierarchy() + "') "
            + "and DATE_FORMAT(TRANSACTION_DATE,'%Y%m%d') <= DATE_FORMAT(NOW(),'%Y%m%d')";
            if (!"-1".equals(dto.getPackageId())) {
                sql = sql + " and PACKAGE_DEF_ID='" + dto.getPackageId() + "'";
                }
            sql = sql + " order by TRANSACTION_DATE";
        List<Object[]> objectListPrepay = getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
        if (objectListPrepay == null || objectListPrepay.isEmpty()) {
            return result;
        } else {
            for (int i = 0; i < objectListPrepay.size(); i++) {
                Object[] resultColsNewMbr = objectListPrepay.get(i);
                SalesReportDetailsDto data = new SalesReportDetailsDto();
                data.setName(Utils.getString(resultColsNewMbr[0]));
                data.setTelephone(Utils.getString(resultColsNewMbr[1]));
                data.setCarPlate(Utils.getString(resultColsNewMbr[2]));
                data.setTransactionDate(Utils.getString(resultColsNewMbr[3]));
                data.setPackageNo(Utils.getString(resultColsNewMbr[4]));
                data.setPackageName(Utils.getString(resultColsNewMbr[5]));
                data.setBalance(Utils.getString(resultColsNewMbr[6]));
                result.add(data);
            }
        }
        return result;
        }
    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      Dec 22, 2015
     * @see com.cap.bts.crmdp.dealer.logistic.dao.IMaintainLogisticDao#getLogisticProcessListByDealerIdAndLogisticType(java.lang.Long, java.lang.Integer)
    **/
    @Override
    public List<LogisticProcess> getLogisticProcessListByDealerIdAndLogisticType(Long dealerId,
        Integer logisticType) throws AppException {
        return super.getListByQuery("from LogisticProcess a where a.dealerId='"+dealerId+"' and a.type="+logisticType+" and a.shippedQuantity!=0");
    }
    
    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      Dec 22, 2015
     * @see com.cap.bts.crmdp.dealer.logistic.dao.IMaintainLogisticDao#getLogisticProcessListByDealerIdAndLogisticType(java.lang.Long, java.lang.Integer)
    **/
    @Override
    public List<Object[]> getLogisticProcessHistoryListByDealerIdAndLogisticType(Long dealerId,
        Integer logisticType) throws AppException {
        String sql="select b.id,a.card_def_id,b.logistic_id,b.received_quantity,b.receive_date from tt_logistic_process a inner join tt_logistic_process_history b where a.id=b.logistic_id and a.dealer_id="+dealerId+" and type="+logisticType+" order by b.id desc";
        return getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sql));
    }

}
